Use row_number() over partition by to keep rows where value is not null, even if it occurs first

Programming
, , ,
1

I need to create a query to return the latest record per ID, prioritizing records where both START_VAL and END_VAL are not NULL if there are multiple records for the latest date.

My current query is:

SELECT *
FROM MY_TABLE
QUALIFY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LOAD_DATE DESC) = 1;

This query returns the latest record per ID, but does not prioritize records where START_VAL and END_VAL are both not NULL. Is there a way to define this sort of logic in the PARTITION clause?