Get latest record for each case-CodePudding

I have table with id,ref_id,name,created_on. I'm trying to get only latest record for each name. Query that I'm trying :

select 
    (case when name = 's1' then created_on end) as serv1,
    (case when name = 's2' then created_on end) as serv2     
from table tsl where id ='6326323'
order by created_on desc

Output that i expect

serv1                 serv2 
----------------------------------
2020-09-14 07:52:22  2020-09-14 09:12:22

output i get

serv1                   serv2
----------------------------------------
2020-09-14 07:52:22      2020-10-14 07:52:22
2020-09-13 07:52:22

CodePudding user response：

Using max helped

select 
    max(case when name = 's1' then created_on end) as serv1,
    max(case when name = 's2' then created_on end) as serv2     
from table tsl where id ='6326323'

CodePudding user response：

By hardcoding the name you are limiting this to only 's1' and 's2' This will give you the latest records for each name:

select * from (
    select *
        row_number() over(partition by name order by created_on desc) as rn
    from
        tsl
    where id ='6326323'
) t
where t.rn = 1