One developer's SQL blog

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.

Twitter

Linkedin

Facebook