how to give priority to particular record in oracle?

Question

I have a table:

id     name    location
1      aaa     home
1      aaa     village
1      aaa     office
2      bbb     village
2      bbb     office
3      ccc     home
3      ccc     office

When I write a query, I should get single record for each having precedence home > office > village

Output:

1 aaa home
2 bbb office
3 ccc home 

Answer 1

You can use row_number() to assign each row a number in a given order per ID. Use a CASE to define the order by location.

SELECT x.id,
       x.name,
       x.location
       FROM (SELECT t.id,
                    t.name,
                    t.location,
                    row_number() OVER (PARTITION BY t.id
                                       ORDER BY CASE location
                                                  WHEN 'home' THEN
                                                    1
                                                  WHEN 'office' THEN
                                                    2
                                                  WHEN 'village' THEN
                                                    3
                                                  ELSE
                                                    4
                                                END)
                    FROM elbat) x
       WHERE x.rn = 1;

Note: If the locations are only exactly the given three, than the CASE isn't needed, as they coincidentally already have the wanted order when sorted lexicographically. But I assume that was just an example and the coincidence might not be given in the real data.

Answer 2

Or you can use a group by:

SELECT 
   t.id, 
   t.name, 
   min( t.location ) keep (dense_rank first order by CASE location
                                                     WHEN 'home' THEN  1
                                                     WHEN 'office' THEN 2
                                                     WHEN 'village' THEN 3
                                                     ELSE  4
                                                     END
                            ) as location
FROM your_table t
GROUP BY 
   t.id, 
   t.name;