SQL Sort Order with Null Values Last

SQL how to make null values come last when sorting ascending

select MyDate
from MyTable
order by case when MyDate is null then 1 else 0 end, MyDate

How to sort a column in ascending order with NULL values at the end?

If you calculate anything on the date field, you'll loose the use of the index on that.

So check for Null:

SELECT 
    startdate, id
FROM 
    YourTable
ORDER BY
    StartDate Is Null,
    StartDate

SQL Sort Order with Null values last

One way is to sort it like this:

ORDER BY 
(CASE WHEN Foo IS NULL THEN 1 ELSE 0 END), Foo

Or: First sort by null, then sort by the Foo contents.

MySQL Orderby a number, Nulls last

MySQL has an undocumented syntax to sort nulls last. Place a minus sign (-) before the column name and switch the ASC to DESC:

SELECT * FROM tablename WHERE visible=1 ORDER BY -position DESC, id DESC

It is essentially the inverse of position DESC placing the NULL values last but otherwise the same as position ASC.

A good reference is here http://troels.arvin.dk/db/rdbms#select-order_by

SQL Server ORDER BY date and nulls last

smalldatetime has range up to June 6, 2079 so you can use

ORDER BY ISNULL(Next_Contact_Date, '2079-06-05T23:59:00')

If no legitimate records will have that date.

If this is not an assumption you fancy relying on a more robust option is sorting on two columns.

ORDER BY CASE WHEN Next_Contact_Date IS NULL THEN 1 ELSE 0 END, Next_Contact_Date

Both of the above suggestions are not able to use an index to avoid a sort however and give similar looking plans.

One other possibility if such an index exists is

SELECT 1 AS Grp, Next_Contact_Date 
FROM T 
WHERE Next_Contact_Date IS NOT NULL
UNION ALL
SELECT 2 AS Grp, Next_Contact_Date 
FROM T 
WHERE Next_Contact_Date IS NULL
ORDER BY Grp, Next_Contact_Date

How to sort NULL values last - for grouping summary rows with group by rollup/cube

Just change

ORDER BY Year, Quarter

to

ORDER BY GROUPING(Year), Year, GROUPING(Quarter), Quarter

Explanation: this function returns 1 for total rows and 0 for all others.

---

Related Topics