CODEWITHSUNDEEP
sql-serversortingselectnullsql-order-by
FMFF
FMFF

Reputation: 1718

SQL SELECT Ordering columns with Null Values

My question is similar to this one: How to display a table order by code (like 01, 02… then null columns)?, but for SQL Server.

In short, I have a SELECT statement, that returns the following: 

ColumnA ColumnB
X       NULL
Y       1
Z       2

..where the ordering is done by ColumnB.

How can we force the (columnB = NULL) type of rows to the bottom? ie, the expected result is this:

ColumnA ColumnB
Y       1
Z       2
X       NULL

Thank you SOF community.

Upvotes: 11

Views: 16829

Answers (3)

Marcello Miorelli
Marcello Miorelli

Reputation: 3668

hoping to help someone, I just wanted to add that I have had a similiar issue, using row_number and partition by - when it is zero put it at the end sort of thing and I used the script below (partial view):

   ,T.MONTHS_TO_AUTOGROWTH
   ,the_closest_event=ROW_NUMBER() OVER (PARTITION BY SERVERID, DRIVE ORDER BY 
                                      CASE WHEN MONTHS_TO_AUTOGROWTH > 0 THEN MONTHS_TO_AUTOGROWTH ELSE 9999 
                                      END )

the result is ordered by MONTHS_TO_AUTOGROWTH but zero comes last

Upvotes: 0

Endy Tjahjono
Endy Tjahjono

Reputation: 24450

You can also use isnull:

select * from thetable order by isnull(columnb, 99999)

isnull will replace null with the value you provide to it, so in this case, if the column is null, it will replace it with 99999. You can set the value to some big number so it will be at the bottom of the order.

Upvotes: 4

Dan
Dan

Reputation: 1509

...or in order to avoid value clashing...

SELECT 
   ColumnA, 
   ColumnB
FROM YourTable
ORDER BY 
   CASE WHEN ColumnB IS NULL THEN 1 ELSE 0 END ASC,
   ColumnB

Upvotes: 29

Related Questions