38

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?

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
Dave Long
  • 9,569
  • 14
  • 59
  • 89

4 Answers4

39

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;
Dave Long
  • 9,569
  • 14
  • 59
  • 89
26

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.

Community
  • 1
  • 1
toxalot
  • 11,260
  • 6
  • 35
  • 58
5

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
M.R.
  • 4,737
  • 3
  • 37
  • 81
  • That won't work in MySQL. The `ISNULL()` function works differently in different versions of SQL. You could use `IFNULL()` to accomplish the same thing in MySQL. See [MySQL manual](http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull). – toxalot Dec 07 '12 at 16:05
  • That example won't yield the sort order the OP is looking for. It replaces `NULL` with `'1'` which will still sort before `'A'`. – toxalot Dec 07 '12 at 16:13
  • The OP stated he wanted to order it by certain criteria - The '1' is just for an example to state how to order it.. i.e. put 'ZZ', and it will be at the end. – M.R. Dec 07 '12 at 20:39
2
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

borrel
  • 911
  • 9
  • 17