Menu
I need to write a sql statement to select all users ordered by lastname, firstname. This is the part I know how to do :) What I don't know how to do is to order by non-null values first. Right now I get this:
null, null
null, null
p1Last, p1First
p2Last, p2First
etc
I need to get:
p1Last, p1First
p2Last, p2First
null, null
null, null
Any thoughts?
523
SQL COALESCE – a function that returns the first defined, i.e. non-NULL value from its argument list. Usually one or more COALESCE function arguments is the column of the table the query is addressed to. Often a subquery is also an argument for a function.
SQL treats NULL values to be less than 0 so while sorting in ascending order, NULL values always appear to be at first.
If you specify the ORDER BY clause, NULL values by default are ordered as less than values that are not NULL. Using the ASC order, a NULL value comes before any non-NULL value; using DESC order, the NULL comes last.
SQLite. In contrast to PostgreSQL and Oracle, SQLite treats NULLs as very small values and puts them first in an ascending sort and last in a descending sort. Starting with SQLite version 3.30. 0, this behavior can also be easily changed using the NULLS FIRST / NULLS LAST option.
See Sort Values Ascending But NULLS Last
basically
SELECT *
FROM @Temp
ORDER BY CASE WHEN LastName IS NULL THEN 1 ELSE 0 END, LastName
ORDER BY CASE WHEN name IS NULL THEN 1 ELSE 0 END, name;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
