CODEWITHSUNDEEP
sql-serversql-order-bycase
Paul
Paul

Reputation: 594

How does work SQL with order by 0?

How does it work?

SELECT * 
FROM table1
ORDER BY 
CASE WHEN field1 is not null 
    then 0
end, 
id DESC

I can replace 0 with other integer values but I still get correct result.

Upvotes: 3

Views: 14158

Answers (2)

AHiggins
AHiggins

Reputation: 7219

As jurergen d pointed out, this reduces the possibilities in your ORDER BY to two candidates: NULL and n. Whether n is 0, 6, 562, or 391842, anything in condition one (NULL) will show up before anything in condition two (n).

If, by "correct result", you mean "things with a NULL in Field1 show up first", then any number, positive or negative, would work as n.

Some sample data: it can be helpful to see what the ORDER BY clause is actually doing, so I'm going to duplicate that CASE statement in the SELECT. 

DECLARE @Table1 TABLE (ID INT IDENTITY(1,1), Field1 INT, FieldSomethingElse VARCHAR(10))
INSERT INTO @Table1 (Field1, FieldSomethingElse) VALUES (1, 'some')
INSERT INTO @Table1 (Field1, FieldSomethingElse) VALUES (2, 'thing')
INSERT INTO @Table1 (Field1, FieldSomethingElse) VALUES (765, 'or')
INSERT INTO @Table1 (Field1, FieldSomethingElse) VALUES (NULL, 'another')
INSERT INTO @Table1 (Field1, FieldSomethingElse) VALUES (NULL, 'thing')


SELECT 
    *, 
    CASE WHEN Field1 IS NOT NULL THEN 'ItDoesn''t Matter What I Put Here, it is the same value for every non-NULL row' END AS ThisIsWhatYouAreSortingBy
FROM @Table1
ORDER BY 
    CASE WHEN Field1 IS NOT NULL THEN 'ItDoesn''t Matter What I Put Here, it is the same value for every non-NULL row' END, ID DESC

The purpose, as you probably already have figured out, is to allow your ORDER BY to use the ID field as the sort, but separate it out so that the rows with a NULL Field1 value come first.

Upvotes: 4

Squirrel
Squirrel

Reputation: 24793

ORDER BY CASE WHEN field1 is not null then 0 end

NULL value will come first before all other value. So 0 or any other value does not matter.

Upvotes: 0

Related Questions