sql - How to remove duplicate ID rows. While removing, use the rows that has NULL value in another column -

while removing duplicate rows same id value, how remove rows has null value in 1 particular column.

note: there other non-duplicate rows (below e.g., 12) has null value , should still selected in result set.

input table:

id  | sale_date  | price ----------------------------- 11   20051020     22.1   11   null         20.1   12   null         20.1   13   20051020     20.1   

expected result:

id  | sale_date | price ----------------------------- 11  20051020    22.1     12  null        20.1     13  20051020    20.1     

assuming have sql server 2008 or above, work you. use row_number , assign values id starting @ max date. value higher 1 lower max date particular id delete row_num greater 1.

check out:

declare @yourtable table (id int,sale_date date, price float);  insert @yourtable values  (11,'20051020',22.1),         (11,null,20.1),         (12,null,20.1),         (13,'20051020',20.1);  cte (     select  *,             row_number() on (partition id order sale_date desc) row_num     @yourtable )  delete cte row_num > 1  select * @yourtable