Sql – How to combine SELECT DISTINCT and ROWNUM in Oracle Query

oraclesql

I need to combine the two MySQL statements below into a single ORACLE query if possible.

The initial query is 

SELECT DISTINCT FIRST_NAME FROM PEOPLE WHERE LAST_NAME IN ("Smith","Jones","Gupta")

then based on each FIRST_NAME returned I query

SELECT * 
FROM PEOPLE 
WHERE FIRST_NAME = {FIRST_NAME} 
  AND LAST_NAME IN ("Smith","Jones","Gupta") 
ORDER BY FIELD(LAST_NAME, "Smith","Jones","Gupta") DESC 
LIMIT 1

The "List of last names" serves as a "default / override" indicator, so I only have one person for each first name, and where multiple rows for the same first name exist, only the Last match from the list of "Last Names" is used.

I need a SQL query that returns the last row from the "in" clause based on the order of the values in the IN(a,b,c). Here is a sample table, and the results I need from the query.

For the Table PEOPLE, with values

LAST_NAME    FIRST_NAME
.....
Smith        Mike    
Smith        Betty
Smith        Jane
Jones        Mike
Jones        Sally
....

I need a query based on DISTINCT FIRST_NAME and LAST_NAME IN ('Smith','Jones') that returns

Betty Smith
Jane Smith
Mike Jones
Sally Jones

Best Answer

You can do it like this:

select first_name, last_name
from (
  select p.first_name,
         p.last_name,
         row_number() over (partition by p.first_name
                            order by case p.last_name
                                     when 'Smith' then 1
                                     when 'Jones' then 2
                                     when 'Gupta' then 3
                                     end desc) as rn
  from people p
  where p.last_name in ('Smith','Jones','Gupta')
)
where rn = 1;

Demo: SQL Fiddle

EDIT

It's not hard to get more columns. I'm sure you could have figured it out with a bit more effort:

select *
from (
  select p.*,
         row_number() over (partition by p.first_name
                            order by case p.last_name
                                     when 'Smith' then 1
                                     when 'Jones' then 2
                                     when 'Gupta' then 3
                                     end desc) as rn
  from people p
  where p.last_name in ('Smith','Jones','Gupta')
)
where rn = 1;
Related Topic