Sql: How to Order Null and Empty Entries to The Front in an Orderby

SQL: How can I order null and empty entries to the front in an orderby?

ORDER BY 
    CASE 
    WHEN Name IS NULL THEN 1 
    WHEN Name = ''    THEN 2 
    ELSE 3 
    END DESC, 
    Name ASC

MySQL Orderby a number, Empty Strings (or 0's) Last

SELECT * 
FROM tablename 
WHERE visible=1 
ORDER BY 
    case when position in('', '0') then 1 else 0 end,
    position ASC, 
    id DESC

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

MySql: ORDER BY ASC, Nulls at the bottom

select album from songs ORDER BY CASE WHEN album IS NULL THEN 1 WHEN album= '' THEN 2 ELSE 3 END DESC, album ASC

It will take care of empty string as well

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 by column ASC, but NULL values first?

Postgres has the NULLS FIRST | LAST modifiers for ORDER BY expression:

... ORDER BY last_updated NULLS FIRST

The typical use case is with descending sort order (DESC), which produces the complete inversion of the default ascending order (ASC) with null values first - which is often not desirable. To sort NULL values last:

... ORDER BY last_updated DESC NULLS LAST

To support the query with an index, make it match:

CREATE INDEX foo_idx ON tbl (last_updated DESC NULLS LAST);

Postgres can read btree indexes backwards, but for some query plans it matters where NULL values are appended. See:

• Performance impact of view on aggregate function vs result set limiting

How to order by column with null values last in entity framework

I would do:

using(var context = new DomainEntities())
{
    var result = context.Users.OrderBy(u => u.LastName == null)
                              .ThenBy(u => u.LastName)
                              .ThenBy(u => u.FirstName == null)
                              .ThenBy(u => u.FirstName);
}

...which should produce reasonable SQL.

EDIT: explanation (taken from Craig's comment):

Because false sorts before true.

ORDER BY columns that are sometimes empty using Active Record & Rails

You're running in to a letter case problem: Your names are all capitalized, but the emails are lowercase, and with most collations, uppercase letters come before lowercase. Check out this trivial example:

#= select * from (values ('b'), ('B'), ('a'), ('A')) t (letter);
 letter
--------
 b
 B
 a
 A

#= select * from (values ('b'), ('B'), ('a'), ('A')) t (letter) order by letter;
 letter
--------
 A
 B
 a
 b

So your query is actually working perfectly, it's just that cxxr@person.com sorts after Josh. To avoid this, you can sort by the lowercase value. Here's a simple version of the data you have:

#= select * from volunteers;
 first_name | last_name |       email
------------+-----------+--------------------
 Josh       | Broger    | jcool@person.com
 Josh       | Kenton    | aj@person.com
 ∅          | ∅         | cxxr@person.com
 Josh       | Broger    | broger@person.com
 Alex       | Diego     | a.diego@person.com

Then to sort using the coalesce you're after:

#= select * from volunteers order by lower(coalesce(first_name, email));
 first_name | last_name |       email
------------+-----------+--------------------
 Alex       | Diego     | a.diego@person.com
 ∅          | ∅         | cxxr@person.com
 Josh       | Broger    | broger@person.com
 Josh       | Broger    | jcool@person.com
 Josh       | Kenton    | aj@person.com

Or for your full version using ActiveRecord:

Volunteer
  .joins(:volunteer_lists)
  .where(
    "(volunteer_lists.organizer_id = ? AND organizer_type = 'Organization') OR (volunteer_lists.organizer_id IN (?) AND organizer_type = 'Collaborative')",
    organization.id, collaboratives
  )
  .order('LOWER(COALESCE("volunteers"."first_name", "volunteers"."last_name", "volunteers"."email"))')

Possible to use SQL to sort by date but put null dates at the back of the results set?

Here's a solution using only standard SQL, not ISNULL(). That function is not standard SQL, and may not work on other brands of RDBMS.

SELECT * FROM myTable
WHERE ...
ORDER BY CASE WHEN myDate IS NULL THEN 1 ELSE 0 END, myDate;

---

Related Topics