Need to fetch data with latest date

Question

I have following table,

----------------------------------------
|person_id   date        pstatus    px1 |
---------------------------------------- 
  1         29|6|2016     null      089E

  1         27|6|2016     Died      null

  2         29|6|2016     null      DFWE

  2         27|6|2016     null      WEWE

  3         29|6|2016     Died      null

From the above table, I need to get following output. If "pstatus" is not null I need to fetch that record corresponding to each person_id, if pstatus null , need to fetch record with latest date.

----------------------------------------
|person_id   date        pstatus    px1 |
---------------------------------------- 

  1         27|6|2016     Died      null

  2         29|6|2016     null      DFWE

  3         29|6|2016     Died      null

Are you _really_ using Postgres **and** Oracle **and** Amazon Redshift? All three of them? — , Jul 04 '16 at 07:46

score 0 · Accepted Answer · answered Jul 04 '16 at 07:36

You could use the row_number window function to enumerate the records per person_id, and select the "last" one - either having a not-null pstatus, or the latest date:

SELECT person_id, date, pstatus, px1
FROM   (SELECT person_id, date, pstatus, px1,
               ROW_NUMBER() OVER (PARTITION BY person_id
                                  ORDER BY CASE WHEN pstatus IS NOT NULL 
                                                THEN 0 
                                                ELSE 1 END ASC, 
                                           date DESC) AS rn
        FROM mytable) t
WHERE  rn = 1

Thanks Mureinik .....This is working for me – Chinnu Jul 04 '16 at 09:13 — Chinnu, Jul 04 '16 at 09:13

score 0 · Answer 2 · answered Jul 04 '16 at 07:37

you need use Row_Number() Over (Partition By ..) to get needed data like this

 with ss as (select 1 as pid , to_date('29.06.2016','dd.mm.yyyy') as dd,null as status, '089E' as px1 from dual union all
             select  1,to_date('27.06.2016','dd.mm.yyyy'),'Died',null from dual union all
             select  2,to_date('29.06.2016','dd.mm.yyyy'),null ,'DFWE' from dual union all
             select  2,to_date('27.06.2016','dd.mm.yyyy'),null ,'WEWE' from dual union all
             select  3,to_date('29.06.2016','dd.mm.yyyy'),'Died' ,null  from dual)
select * from (
select ss.*,
        Row_Number() Over (Partition By pid Order By status nulls last, dd desc) Rn
  from ss

  ) where Rn = 1

score 0 · Answer 3 · answered Jul 04 '16 at 07:40

Try this;)

select t1.*
from yourtable t1
join (
    select max(date) as "date", max(pstatus) as pstatus, person_id
    from yourtable
    group by person_id
) t2
on t1.person_id = t2.person_id
and ((t2.pstatus is not null and t1.pstatus = t2.pstatus) or t1.date = t2.date)

Need to fetch data with latest date

3 Answers3