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

Answer 1

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