Search code examples
mysqlsqldremio

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

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;
Solution

  • 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