CODEWITHSUNDEEP
sql-serverrank
Toby
Toby

Reputation: 145

SQL Server 2008 Rank with Nulls last

I have the below extract from a long query that I wrote. I'm trying to create a Rank function to rank so I then can filter with a sub query

How would one utilize rank where blanks and null would be ranked last?

Rank 

id    Category        Review
 1      Site           "null value"
 1     "blank value"      Yes
 1     Sited            Yes

Expected Output:

id    Category        Review            Rnk
 1      Site           "null value"       3
 1     "blank value"      Yes             2
 1     Sited            Yes               1

Upvotes: 1

Views: 2680

Answers (3)

Matt
Matt

Reputation: 14341

DECLARE @Table AS TABLE (Id INT, Category VARCHAR(15), Review VARCHAR(15))
INSERT INTO @Table VALUES (1,'Site',NULL),(1,NULL,'Yes'),(1,'Sited','Yes')

SELECT
    Id
    ,Category
    ,Review
    ,Rnk = ROW_NUMBER() OVER (ORDER BY
          CASE WHEN ISNULL(Review,'') = '' THEN 1 ELSE 0 END
          ,CASE WHEN ISNULL(Category,'') = '' THEN 1 ELSE 0 END)
FROM
    @Table
ORDER BY
    Rnk DESC

Use a CASE Expressions to test for NULL values in the fields and if null make it a larger value than not null. Do this for Category and Review. If you want ties switch ROW_NUMBER() to DENSE_RANK(). Add partition and other order by information to refine for more test cases.

In your Case Expressions you can do all kinds of things like if you want to sort Nulls absolutely last and blanks (empty string) second to last you could do something like

CASE
  WHEN Review IS NULL THEN 2 --Nulls last
  WHEN Review = '' THEN 1 -- Empty String 2nd To Last
  ELSE 0 --
END

Upvotes: 4

Pops
Pops

Reputation: 498

If you order by desc, the nulls will come up last.

DENSE_RANK () OVER (PARTITION BY ID, REVIEW ORDER BY F.TypeOfReview desc, OnSiteChoices desc) Rnk

Upvotes: 0

DVT
DVT

Reputation: 3127

SELECT
    a.id
    , a.Category
    , a.Review
    , RANK() OVER (ORDER BY a.CategoryRank DESC, a.Category, a.Review DESC, a.Review) AS Rnk
FROM
    (
    SELECT 
        id, Category, Review,
        CASE
            WHEN Category IS NULL THEN 0
            ELSE 1
        END AS CategoryRank,
        CASE
            WHEN Review='' THEN 0
            ELSE 1
        END AS ReviewRank       
    ) a

Upvotes: 0

Related Questions