SQL Server - Select most recent records with condition -
i have table this.
table :
id enrolldate exitdate 1 4/1/16 8/30/16 2 1/1/16 null 2 1/1/16 7/3/16 3 2/1/16 8/1/16 3 2/1/16 9/1/16 4 1/1/16 12/12/16 4 1/1/16 12/12/16 4 1/1/16 12/12/16 4 1/1/16 null 5 5/1/16 11/12/16 5 5/1/16 11/12/16 5 5/1/16 11/12/16 need select recent records these conditions.
- one , 1 record has recent enroll date - select that
- two or more share same recent enroll date , 1 , 1 record has either null exit date or recent exit date - select record null. if no null record pick record recent exit date
- two or more same enroll , exit date - if case exists, don't select record
so expected result above table should :
id enrolldate exitdate 1 4/1/16 8/30/16 2 1/1/16 null 3 2/1/16 9/1/16 4 1/1/16 null i wrote query group by. not sure how select conditions 2 , 3.
select t1.* table t1 inner join(select id,max(enrolldate) maxentrydate table group id)t2 on enrolldate = t2.maxentrydate , t1.id=t2.id please let me know best way this.
using rank() window function, think it's possible.
this untested, should work:
select t.id, t.enrolldate, t.exitdate (select t.*, rank() over( partition id order enrolldate desc, case when exitdate null 1 else 2 end, exitdate desc) rnk tbl t) t t.rnk = 1 group t.id, t.enrolldate, t.exitdate having count(*) = 1 the basic idea rank() window function rank "recent" rows value of 1, filter on in outer query's where clause.
if more 1 row have same "most recent" data, share same rank of 1, filtered out having count(*) = 1 clause.
