I have some challenging result set coming back that I need to massage. Some rows come back duplicate even including ID's but are different by just one field at the end
Here is the example
| ID | Val1 | Val2 | Val3 | Val4 | Val5 | SomeVal |
|---|---|---|---|---|---|---|
| 1111 | 'Value0 1' | 'Value0 2' | 'Value0 3' | 'Value0 4' | 'Value0 5' | NULL |
| 1111 | 'Value0 1' | 'Value0 2' | 'Value0 3' | 'Value0 4' | 'Value0 5' | 'Some Value0' |
| 2222 | 'Value1 1' | 'Value1 2' | 'Value1 3' | 'Value1 4' | 'Value1 5' | NULL |
| 2222 | 'Value1 1' | 'Value1 2' | 'Value1 3' | 'Value1 4' | 'Value1 5' | 'Some Value1' |
| 3333 | 'Value3 1' | 'Value3 2' | 'Value3 3' | 'Value3 4' | 'Value3 5' | NULL |
| 4444 | 'Value4 1' | 'Value4 2' | 'Value4 3' | 'Value4 4' | 'Value4 5' | NULL |
In the example above I would like to filter out rows with NULL at the end that have duplicate values for other fields and keep those that have 'Some Value' and also keep rows with NULLs at the end that have no duplicates up top
Thus, I would like to filter out these rows and keep the rest
| ID | Val1 | Val2 | Val3 | Val4 | Val5 | SomeVal |
|---|---|---|---|---|---|---|
| 1111 | 'Value0 1' | 'Value0 2' | 'Value0 3' | 'Value0 4' | 'Value0 5' | NULL |
| 2222 | 'Value1 1' | 'Value1 2' | 'Value1 3' | 'Value1 4' | 'Value1 5' | NULL |
What can I do? I was considering LEAD function but very dubious about it
Thank you in advance
Here is the answer that worked, thank you very much for your help
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CASE WHEN SomeVal IS NULL THEN 1 ELSE 0 END)