Some Case statement issue

Question

I have two tables that has data like

table1

Id  id_nm
1   per
2   per
3   org

table2

Id Lst_id l_nm  up_dt
1   22    abc   9/10/2015
1   21    abs   10/12/2016
2   21    xzc   10/12/2013
2   23    xyz   10/21/2013
2   23    xnh   01/12/2013

Need to pick the l_nm where lst_id is 22. If that is not present then we need to pick the l_nm with the most recent updated date.

Id lst_id lnm   up_dt
1   22    abc   9/10/2015
2   23    xyz   10/21/2013

can any one please help me in implementing it.

Answer 1

Simple way is to use row_number with a window clause to generate a custom sort order:

select id, lst_id, l_nm as lnm, up_dt
from (
  select id
        ,lst_id
        ,l_nm
        ,up_dt
        ,row_number()
         over (partition by id
               order by case when lst_id = 22 then 1 else 2 end
                       ,up_dt desc) as rn    
  from table2
) where rn = 1;