CODEWITHSUNDEEP
sqlsql-serverrow-number
jackstraw22
jackstraw22

Reputation: 641

Need window function to order null and non-null values

In the following dataset I'm trying to remove the ES Hou entity with the NULL LocName. I want to keep the other three records. I've been trying to do this with a row_number() function but I can't figure out how to get the nonnull value to get a higher rank than the null value. Any suggestions?

    PracticeId  LastName  EntityId LocId  EntityName  LocName  PracName
    53681       Thomas    3194     2222   ECS Tampa   NULL     NULL
    53681       Thomas    3195     2222   ECS Atlanta NULL     NULL
    53681       Thomas    3222     2222   ES Hou      NULL     NULL
    53681       Thomas    3222     2434   ES Hou      ECS Hou  Regional Med

Upvotes: 2

Views: 3400

Answers (3)

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

You can use row_number with a case to create a custom order where null comes after the non-null values. We could've sorted the locname in ascending order but that will pick the one of the row in a particular order (which was not dictated in the question).

select * from (
select
    t.*,
    row_number() over (partition by entityname
                        order by case when locname is null then 1 else 0 end) rn
from your_table t
) t where rn = 1;

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93744

In Row_Number window function order the LocName in descending order, NULL values will sorted at the end

Select * from
(
select Row_Number()over(Partition by EntityName Order by LocName desc) Rn, *
From yourtable 
) A
Where Rn = 1

Note : If you have more than one NOT NULL LocName for an EntityName then it will bring only one record

Upvotes: 3

mhep
mhep

Reputation: 2139

You could use a CASE or IIF statement in your ROW_NUMBER function to order NULL values as appropriate.

SELECT
    *
,   ROW_NUMBER() OVER(ORDER BY IIF(LocName IS NULL, 0, 1), SomeOtherColumnToSort) AS SortOrder

FROM
    Table

Upvotes: 0

Related Questions