Get all distinct values from column1 in case of 2 similar rows, get the one with column2 not null

Question

I have a large table as dataset. If there are 2 similar rows with same date and id then how do I get the row for which another column value is not null?

SELECT *, row_number() 
   over (partition by id order by date desc) rowNumber 
FROM table where rowNumber = 1;

Why did you think row number a solution to this , I would have thought a not null test on ciol2 would suffice. Sample data and expected outcome as text would help clarify. — P.Salmon, Sep 19 '22 at 12:32
Hi, there are multiple rows initially for a single id, get the latest dated row for a particular id because eventually I need one row with latest timestamp. But incase there are duplicate after that filter for same time value then get the row for which col2 is non null — MKM, Sep 20 '22 at 03:22

Pralhad K · Answer 1 · 2022-09-19T13:27:34.337

0

Please try this query:

select * from table where (id, date) in 
(select id, date from table group by id, table having count(1) > 1) 
and value is not null;

edited Sep 19 '22 at 13:27

answered Sep 19 '22 at 13:26

Pralhad K

11
1

score 0 · Accepted Answer · answered Oct 14 '22 at 07:56

0

Ordering by the column which has 2 values(1 row with null and 1 without null) helped

default ordering is ascending and NULLS LAST is the default for ascending order and rowNumer=1 would give the row without null

answered Oct 14 '22 at 07:56

MKM

1
2

Get all distinct values from column1 in case of 2 similar rows, get the one with column2 not null

2 Answers2