how to use case..when on oracle database

Question

I am running a sql statement on an oracle 11g database. the output is like the following with some joins.(there are other columns but the important columns are these ones)

id | document       | address
---+----------------+--------------
1  | identity_card  | hudson street
1  | driver_licence | hudson street
1  | passport       | hudson street
1  | identity_card  | mike street
1  | driver_licence | mike street
1  | passport       | mike street
2  | driver_licence | laura street
2  | passport       | laura street
3  | passport       | amy street
3  | identity_card  | bug street
3  | identity_card  | apple street
3  | passport       | bug street
4  | driver_licence | 16th street
5  | identity_card  | 21st street

however I want it to be something like it:

id | document       | address
---+----------------+--------------
1  | identity_card  | hudson street
1  | identity_card  | mike street
2  | passport       | laura street
3  | identity_card  | bug street
3  | identity_card  | apple street
4  | driver_licence | 16th street
5  | identity_card  | 21st street

as you might seen I am looking at the each IDs, if the ID has identity_card as document, I take that line. If does not have identity_card but has passport, I take that line, if does not have identity_card and passport I take driver_licence.

how can I do such thing? I think I can use "case...when" but I could not figure out how to apply "case...when" to my case.

thanks in advance!

Answer 1

To your existing query, add:

, rank() over (partition by id order by case document when 'identity_card' then 1
                                                      when 'passport'      then 2
                                                      when ......................
                                                      ...........................
                                        end) as rnk

Then, in an outer query, select everything and add

where rnk = 1

Answer 2

One method uses dense_rank():

select t.*
from (select t.*,
             dense_rank() over (partition by id
                                order by case when document = 'identity_card' then 1
                                              when document = 'passport' then 2
                                              when document = 'drivers_license' then 3
                                              else 4
                                         end) as seqnum
      from t
     ) t
where seqnum = 1;