Function equivalent to "DISTINCT ON" in Oracle BD?

Asked

Viewed 6,186 times

7

In Postgres, you can make distinct apply to only 1 select field using the DISTINC ON, how can I reproduce this function in an Oracle database?

Follow an example query:

SELECT DISTINCT ON (CITY) CITY, STATE, ZIPCODE, ROW_ID FROM db.BILLADDRESS

I’d like to ride it more or less like this:

SELECT DISTINCT ON (CITY) CITY || ';' || STATE || ';' || ZIPCODE || ';' || ROW_ID FROM db.BILLADDRESS
sql oracle postgresql

2 answers

8


In accordance with is response from SO-en (forgive me I translated quickly by eye, as soon as possible review if there is any failure):

The same effect can be replicated on Oracle or using first_value() or using one of the rank() or row_number().

Both variants also work on Postgresql.

first_value()

select distinct col1, 
first_value(col2) over (partition by col1 order by col2 asc)
from tmp

first_value takes the first value for the partition, but repeats for each line, so it is necessary to use it in combination with distinct to get a single row for each partition.

row_number() / rank()

select col1, col2 from (
  select col1, col2, 
  row_number() over (partition by col1 order by col2 asc) as rownumber 
  from tmp
) foo
where rownumber = 1

Barter row_number() with rank() this is an example produces the same result.

A feature of the variation is that you can use to search for the N first for a specific partition (e.g. "3 latest updates") simply changing rownumber = 1 for rownumber <= N.

  • I saw this question, but my problem is using select in multiple columns and not just 2. I tried some variations like this SELECT DISTINCT FIRST_VALUE(CITY) OVER (PARTITION BY CITY ORDER BY 1) AS CITY, STATE, ZIPCODE, ROW_ID FROM db.BILLADDRESS but it didn’t work.

  • @Eduardosilva I do not understand well of Oracle, but in my view the second example select col1, col2 from (&#xA; select col1, col2, &#xA; row_number() over ... is fully functional, you could just switch to something like: select col1, col2, col3 from (&#xA; select col1, col2, col3,&#xA; row_number() over .... Forgive me if I don’t understand.

  • 1

    I got the result I was looking for by adding the excerpt FIRST_VALUE(col2) OVER (PARTITION BY col1) for each column of my select (col2, col3, ..), I will add an answer with the query in question, thank you very much!

4

With the help of the @Guilherme Nascimento I managed to assemble the SELECT correctly:

SELECT DISTINCT CITY || ';' || 
    FIRST_VALUE(STATE) OVER (PARTITION BY CITY) || ';' || 
    FIRST_VALUE(ZIPCODE) OVER (PARTITION BY CITY) || ';' || 
    FIRST_VALUE(ROW_ID) OVER (PARTITION BY CITY) 
  FROM db.BILLADDRESS 
ORDER BY 1

Repeating the FIRST_VALUE(colX) OVER (PARTITION BY col1) for every column of mine SELECT, where colX is the column in which you want to apply the DISTINCT.

Browser other questions tagged sql oracle postgresql

You are not signed in. Login or sign up in order to post.