0

I have a table like below:

id | description | expiration date
------------------------
1  | ............| 2016-02-09 00:00:00  
------------------------
1  | ............| 2099-09-09 00:00:00  
------------------------
1  | ............|  
------------------------
2  | ............| 2016-02-09 00:00:00  
------------------------
2  | ............| 2099-09-09 00:00:00  
------------------------
3  | ............| 2099-09-09 00:00:00
  1. If an ID occurs multiple times, the one with no expiration date will be kept, like in ID1. But in ID2, both records have expiration date, the one with bigger number will be kept. So that each id will have one row only, the result will be:
id | description | expiration date
------------------------
1  | ............|  
------------------------
2  | ............| 2099-09-09 00:00:00  
------------------------
3  | ............| 2099-09-09 00:00:00

It is easy to implement in programming language, but I'm not able to write an elegant SQL command. Thanks.

user8142520
  • 751
  • 1
  • 6
  • 20

1 Answers1

1

You can use row_number()

select * from
(
select id, description,expiration_date,
  row_number() over(partition by id order by case when expiration_date is null then 1 else 2 end asc, expiration_date desc) as rn
from tablename
)A where rn=1
Fahmi
  • 37,315
  • 5
  • 22
  • 31