mysql - 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?

question from:https://stackoverflow.com/questions/5993109/order-by-asc-with-nulls-at-the-bottom

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;