In Postgres, you can have distinct apply to just 1 field of the select using DISTINC ON , how can I reproduce this function in an Oracle database?
Here is an example query:
SELECT DISTINCT ON (CITY) CITY, STATE, ZIPCODE, ROW_ID FROM db.BILLADDRESS
I would like to mount it more or less like this:
SELECT DISTINCT ON (CITY) CITY || ';' || STATE || ';' || ZIPCODE || ';' || ROW_ID FROM db.BILLADDRESS
As per is SO-en's response (pardon me I have quickly translated my eye as soon as possible if there is revision some failure):
The same effect can be replicated in Oracle or using first_value() or using one of rank() or row_number() .
Both variants also work in 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 line 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
Exchange row_number() with rank() This is an example produces the same result.
One variation feature is that you can use to fetch the N first
for a specific partition
(eg "last 3 updates") by simply changing rownumber = 1 by rownumber <= N .
With the help of the @Guilherme Nascimento answer, I was able to mount 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 FIRST_VALUE(colX) OVER (PARTITION BY col1) for each column of my SELECT , where colX is the column in which you want to apply DISTINCT .