Hard question to verbalize in the title with character constraint, sorry.

I have Table1 with the fields PartitionField, ImportantField1, ImportantField2, DateField. ImportantField1 or ImportantField2 can be null but never are both null for the same record, and both can have data on the same record.

I'm doing a ROW_NUMBER() OVER (PARTITION BY PartitionField ORDER BY DateField) AS RowId so that I can take latest record by date.

But what I actually want is the latest record by date where both ImportantField1 and ImportantField2 are not null, or where if the latest record by date's ImportantField1 is null then also get the latest record after that where ImportantField1 is not null.

(Same thing for ImportantField2 as well, if ImportantField2 is null in the latest record by date, then I additionally want the next latest record of that partition where ImportantField2 is not null.)

Example Table:

Example Table

In this case I'd want the highlighted rows: just the latest if both ImportantField1 and ImportantField2 are not null, otherwise the latest record (which has a null important field) and the next latest whose matching important field is not null, per partition.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.