ORDER BY ASC with Nulls at the Bottom

Question

I'm writing an SQL query that connects a schools table to a districts table. Simple One-To-Many relationship where each school is attached to one district. My query is as follows:

SELECT 
    schools.id AS schoolid,
    schools.name AS school, 
    districts.id AS districtid, 
    districts.name AS district
FROM sms_schools AS schools
    LEFT JOIN sms_districts AS districts ON schools.districtid = districts.id
WHERE 1 = 1
ORDER BY districts.name, schools.name

The reason I did a left join is because not every school is attached to a district. For example one school may be home schooled that may contain all students that are home schooled. That wouldn't be in a district.

So what I would like to do is use the ORDER BY to order as it is by district name and then school name. The only problem is that I want the null district to be at the bottom so that I can then use a group called 'Other' at the end of my output.

Is it possible to order by ascending with nulls at the end of the output?

Answer 1

Only 1 minute after asking the question I found my answer. In the order by clause use case to make nulls have a higher value than anything else:

 ORDER BY (CASE WHEN districts.id IS NULL then 1 ELSE 0 END),districts.name, schools.name;

Answer 2

You could use the ISNULL() function.

From the MySQL manual:

ISNULL(expr)

If expr is NULL, ISNULL() returns 1, otherwise it returns 0.

For example:

ORDER BY ISNULL(districts.name), districts.name, schools.name

I like to use this instead of the CASE option for MySQL. Just be aware that it's not portable since ISNULL() is not standard SQL and functions differently in other versions of SQL.

Answer 3

Nulls by default occur at the top, but you can use IsNull to assign default values, that will put it in the position you require...

SELECT schools.id AS schoolid,schools.name AS school, districts.id AS districtid, districts.name AS district FROM sms_schools AS schools LEFT JOIN sms_districts AS districts ON schools.districtid = districts.id WHERE 1 = 1 
ORDER BY isnull(districts.name,'1'), schools.name 

Answer 4

SELECT
 schools.id AS schoolid,
 schools.name AS school,
 districts.id AS districtid,
 districts.name AS district,
 if(schools.districtid IS NULL,1,0) as sort 
FROM sms_schools AS schools
LEFT JOIN sms_districts AS districts 
 ON schools.districtid = districts.id
WHERE 1 = 1
ORDER BY sort, districts.name, schools.name

put any more sort rules insite the 'new' colunm and use any number hide the field in your code, test if it is possebele to sort on the if dirctly(order by if...)

good luck