SQL How to Make Null Values Come Last When Sorting Ascending

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 ascending but null values descending

Use a case statement in order by.

Query

select * from myTable
order by case when ContactDate is null then 1
else 0 end,ContactDate;

Fiddle demo

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

Sort NULL values to the end of a table

NULL values are sorted last in default ascending order. You don't have to do anything extra.

The issue applies to descending order, which is the perfect inverse and thus sorts NULL values on top.

PostgreSQL 8.3 introduced NULLS LAST:

ORDER BY somevalue DESC NULLS LAST

For PostgreSQL 8.2 and older or other RDBMS without this standard SQL feature:

ORDER BY (somevalue IS NULL), somevalue DESC

FALSE sorts before TRUE, so NULL values come last, just like in the example above.

See:

• Sort by column ASC, but NULL values first?
• The manual on SELECT

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.

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

why result shows some grade value as null

Single quotes in MySQL denote string literals. For database identifiers, such as column or table names, use double quotes, backticks, or nothing at all, if not required. Here you don't need to escape the alias. In addition, you generally cannot refer to an alias in the WHERE clause. But, MySQL has overloaded its HAVING operator to allow it to refer to an alias. Putting this all together, we can use this version:

SELECT job_id,
       CASE job_id WHEN 'AD_PRES' THEN 'A'
                   WHEN 'ST_MAN'  THEN 'B'
                   WHEN 'IT_PROG' THEN 'C'
       END AS grade
FROM employees
HAVING grade IS NOT NULL;

Note that you could also phrase this as:

SELECT job_id,
       CASE job_id WHEN 'AD_PRES' THEN 'A'
                   WHEN 'ST_MAN'  THEN 'B'
                   WHEN 'IT_PROG' THEN 'C'
       END AS grade
FROM employees
WHERE job_id IN ('AD_PRES', 'ST_MAN', 'IT_PROG');

This also works because grade would only be non NULL if the job_id were one of these three values.

---

Related Topics