Only count null rows when it's unique

Question

I want to be able to pull data when it either has data or if it's a unique record with null. Is it possible to do this without MAX?

Name	Class	Grade
John	Math	100
Joey	Math	Null
Joey	Science	100
Joey	Science	Null
Joey	Science	95

Since Joey only has one Math, it should still be in the query results. On the other hand, since there are two science classes for Joey, the null would be filtered out.

Results should be:

Name	Class	Grade
John	Math	100
Joey	Math	Null
Joey	Science	100
Joey	Science	95

I tried putting a query together, but didn't have much luck:

Select distinct Name, Class, Grade,
ROW_NUMBER() over (PARTITION by Name, Class order by Name asc) as RowNum
from Data
Where RowNum = 1

This is a very simplified version of a query that pulls in 20 different columns, so I'd like to avoid groups and MAX, if at all possible. I appreciate any help! Thank you.

Answer 1

You are close, but need two important changes:

• You need a subquery to filter by the results of the row_number().
• The window function needs to do what you really want.

My understanding is that you want either all non-NULL grades. Or, if all grades are NULL, then return those:

select Name, Class, Grade
from (Select d.*,
             rank() over (partition by Name, Class
                          order by (case when grade is not null then 1 else 2 end)
                         ) as seqnum
      from Data d
     ) d
Where seqnum = 1