Incorrect sorting order of NULL values with a SQL view

Question

I am trying return design an SQL View which returns TOP 3 records from a table in ascending order considering NULL values in the table as the largest value so that I can discard the null values from top 3 results if there are more than three records.

I was able to successfully do it using following query but for reasons it is misbehaving w.r.t the third record is that if the third and fourth record in my table are same then my view is returning a NULL value are the third record which is kind of weird.

For Example Consider I have a column in my table named Grades with values 23,45,19,35,35

and when I run my view it should return top 3 values in ascending order which are 19,23,35 but for some reasons my query returns 19,23,NULL and the query works fine if my third record that is 35 is distinct ie if the Grades are 23,45,19,35,40 then it works fine and returns 19,23,35

My Query is as follows

CREATE VIEW [dbo].[test]
AS

SELECT distinct

   CC.Grades,
   CC.Term,  
   FROM CanadianCrudes CC
CROSS APPLY (SELECT TOP 3  Grades
             FROM CanadianCrudes iCC
             WHERE CC.Term = iCC.Term 
             ORDER BY case 
                       when iCC.Grades is null 
                        then 1 
                        else 0 end, iCC.Grades asc ) iCC

MarkD · Accepted Answer · 2014-04-16 15:56:10Z

2

Assuming your grades have a limit to how high they can go could you use an isnull in your orderby and set a default high value for null occurences and a tie breaker for when two values have the same rank (e.g 35)

e.g

CREATE VIEW [dbo].[test]
AS

SELECT distinct

   CC.Grades,
   CC.Term,  
   FROM CanadianCrudes CC
CROSS APPLY (SELECT TOP 3  Grades
             FROM CanadianCrudes iCC
             WHERE CC.Term = iCC.Term 
             ORDER BY isnull(iCC.Grades, 150), ID

answered Apr 16, 2014 at 15:56

MarkD

1,05312 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DoIt Over a year ago

how do I use a tie breaker

MarkD Over a year ago

Well its just a second item in theorder by clause. I used id in my example so I you have two of the same grade it would move onto the next column in your order by and decide which of the two of them goes highest on the list

M.Ali · Accepted Answer · 2014-04-16 16:09:23Z

1

Important Note

You might have ordered results in a View's Definition but it does not guarantee ordered result when you actually select from view.

In your case it will pick the tops 3 values for each Grade but the order in which results and returned and displayed is not guaranteed, Unless you use Order By in your select statement when selecting from view.

Test Data

DECLARE @TABLE TABLE(ID INT)
INSERT INTO @TABLE VALUES 
(1), (2),(3),(5),(NULL),(NULL)

Query

SELECT *
FROM @TABLE
ORDER BY CASE WHEN ID IS NULL THEN 100000 ELSE ID END ASC

Result Set

╔══════╗
║  ID  ║
╠══════╣
║ 1    ║
║ 2    ║
║ 3    ║
║ 5    ║
║ NULL ║
║ NULL ║
╚══════╝

edited Apr 16, 2014 at 16:09

answered Apr 16, 2014 at 15:57

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

6 Comments

Obsidian Phoenix Over a year ago

The OP wanted the Nulls last, not first, and was confused why his code was placing a Null in his top 3 results.

M.Ali Over a year ago

@ObsidianPhoenix if an answer is wrong you need to hold your horses and give OPs a chance to correct it, Down voting should be used carefully.

DoIt Over a year ago

@M.Ali Thanks and the above case works fine but as in my question when there are two third values then it returns the third value as NULL. In your example 1 2 3 5 NULL NULL works fine but when the values are 1 2 3 3 NULL NULL then my view returns 1 2 NULL provided I just want only the top three values unlike in your answer.

DoIt Over a year ago

@M.Ali Even if there is no NULL in my values it still returns a NULL if there are multiple values for number 3 spot and Also I was able to get the same results you got with the query in my question.

Obsidian Phoenix Over a year ago

@M.Ali I disagree. Your answer was wrong so I downvoted. So long as I'm willing to monitor the answer and remove the downvote if it's updated to a correct answer, then it's fine. Even then though, the answer was still wrong.

|

Collectives™ on Stack Overflow

Incorrect sorting order of NULL values with a SQL view

2 Answers 2

2 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related