Every now and then I need to sort data by a numeric value and NULLs always come first. And I want them last.
Let’s set up a table with some data in it:
CREATE TABLE unordered_table ( id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, ord INT, txt VARCHAR(15) ) GO INSERT INTO unordered_table (ord, txt) VALUES (3, '3rd value'), (1, '1st value'), (NULL, 'NULL value'), (2, '2nd value'), (-1, 'negative value') GO
I want to sort data by ord column:
SELECT ord, txt FROM unordered_table ORDER BY ord ASC
which produces:
Now, i want to move NULLs to the end:
SELECT ord, txt FROM unordered_table ORDER BY -ord DESC
which produces:
This -ord DESC is basically multiplying ord by -1, hence reversing the order, and sort DESC will put them back in ASC order. DESC will also put the NULL values at the end, and we get the result we’re after.
0 Comments.