Geek Answers Handbook

SQL SELECT Order columns with null values

My question is like this: How do I display the order of a table by code (e.g. 01, 02 ... then zero 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 in column B.

How can we force rows (columnB = NULL) at the bottom? those. Expected Result:

 ColumnA ColumnB Y 1 Z 2 X NULL

Thank you SOF community.

+8
sorting null sql-server select sql-order-by
source share
3 answers

... or to avoid a clash of values ​​...

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

You can also use isnull:

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

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

+2
source share

hoping to help someone, I just wanted to add that I had a similar problem using row_number and the by section - when it is zero put it at the end. 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 MONTHS_TO_AUTOGROWTH, but zero comes last

0
source share

More articles:


All Articles