昇順ソートでNULLを後ろにもってくる方法

MySQLの昇順ソートの問題

それは、昇順でソートすると、基準カラムがNULLの場合、それが先頭に来ることだ。実装としては正しいかもしれないが、NULLが最後に来てほしい場合もある。
しかし、MySQLには(少なくとも4.1系には)それを指定するオプションがない。よって、昇順にソートすると次のようになる。

  • 例:面接に予約した学生を予約日順に昇順ソートする
SELECT students.id,held_date_time
FROM students
LEFT JOIN interviews on students.interview_id = interviews.id
ORDER BY held_date_time;

+----+---------------------+
| id | held_date_time      |
+----+---------------------+
|  2 | NULL                |
|  4 | NULL                |
|  6 | NULL                |
|  9 | NULL                |
| 12 | NULL                |
| 13 | NULL                |
| 56 | NULL                |
| 57 | NULL                |
| 58 | NULL                |
|  8 | 2007-09-01 09:00:00 |
|  5 | 2007-09-10 14:00:00 |
| 14 | 2007-09-10 14:00:00 |
| 15 | 2007-09-10 14:00:00 |
| 10 | 2007-10-18 20:00:00 |
| 55 | 2007-11-07 15:00:00 |
|  7 | 2007-12-08 13:00:00 |
+----+---------------------+

本当は、2007-12-08 13:00:00の後に、NULLの人がずらずら並んでほしい。

昇順ソートでNULLを後ろにもってくる

解決策の1つとして、NULLの場合は○○とみなすという風にして、ソートをだますことにする。つまり、NULLだったら、それはずっと未来のこと(昇順で後ろにくる日)とみなすということだ。
具体的には次のようになる。

SELECT students.id,held_date_time,
FROM students
LEFT JOIN interviews ON students.interview_id = interviews.id
ORDER BY CASE WHEN held_date_time IS NULL
           THEN DATE_ADD(NOW(),INTERVAL 1 YEAR)
           ELSE held_date_time
         END
;

+----+---------------------+---------------------+
| id | held_date_time      | for_order           |
+----+---------------------+---------------------+
|  8 | 2007-09-01 09:00:00 | 2007-09-01 09:00:00 |
|  5 | 2007-09-10 14:00:00 | 2007-09-10 14:00:00 |
| 14 | 2007-09-10 14:00:00 | 2007-09-10 14:00:00 |
| 15 | 2007-09-10 14:00:00 | 2007-09-10 14:00:00 |
| 10 | 2007-10-18 20:00:00 | 2007-10-18 20:00:00 |
| 55 | 2007-11-07 15:00:00 | 2007-11-07 15:00:00 |
|  7 | 2007-12-08 13:00:00 | 2007-12-08 13:00:00 |
| 58 | NULL                | 2009-01-04 09:32:01 |
|  9 | NULL                | 2009-01-04 09:32:01 |
| 12 | NULL                | 2009-01-04 09:32:01 |
| 13 | NULL                | 2009-01-04 09:32:01 |
|  2 | NULL                | 2009-01-04 09:32:01 |
| 56 | NULL                | 2009-01-04 09:32:01 |
|  4 | NULL                | 2009-01-04 09:32:01 |
| 57 | NULL                | 2009-01-04 09:32:01 |
|  6 | NULL                | 2009-01-04 09:32:01 |
+----+---------------------+---------------------+

場当たり的な気もしますが、このような機能が付いているRDBMSのコードを読んでみるのもいいかもなー

  • 参考元

MySQL で NULL を一番最後にして昇順にソートする

追記Railsで使う際の注意(find_by_sqlの注意かな)

上記のSQLRailsで使う場合(何もRailsに限らないと思うが)、注意する点がある。例えば、上記を実行する際に、以下のようなコードを書いたとする。

  • バグありのコード
@students = Student.find_by_sql(<<-SQL)
      SELECT     *,
      FROM       students
      LEFT JOIN  interviews ON students.interview_id = interviews.id
      ORDER BY   CASE WHEN held_date_time IS NULL
                   THEN DATE_ADD(NOW(),INTERVAL 1 YEAR)
                   ELSE held_date_time
                 END 
    SQL

だが、この状態で次のコードを実行してみる。

@students.first.id #=> 112
Student.find(112)   # => ActiveRecord::RecordNotFound: Couldn't find Student with ID=112
@students.first.interview.id #=> 112

何故か、Studentオブジェクトから引いたidを使って検索をかけても、エラーが生じてしまう。実は、上記の1行目の結果のidは、元のSQLで言えば、students.idでなく、interviews.idなのである。(3行目の結果からそれが分かる)
このようなエラーが起こると、@studentsをビューで表示した際に、以下のようなコードでエラーが発生する。

link_to "詳細",:action => show,:id => @students.first #=> idをもつStudentオブジェクトが存在しないためエラー

何故上記のようなことが起こるのか

これは、簡単で、「バグありのコード」の結果の@studentsは、LEFT JOINした結果であって、純粋なStudentオブジェクトではないから。つまり、JOINで連結されるカラムのheld_date_timeやdummyも持ってしまっている。
これは、「バグありのコードの直後に以下を実行すると分かる」

@students.first.dummy  #=> "2007-09-01 09:00:00"
@students.first.held_date_time #=> "2007-09-01 09:00:00"

よって、理論上@students.firstの中には、students.idとinterviews.idが含まれることになる。しかし、結果のStudentオブジェクトにはどちらもidという名で入るため、どちらかが上書きされるはめになる。(ハッシュは同じキーをもてないため)


よって、@students.first.idは、students.idでなく、interviews.idを参照し、@students.fisrtから直接Studentオブジェクトのidを得ることはできない。

解決策

上記の問題は、JOINの連結結果を取得してるから起こるのであって、結果から、studentsのカラムだけ集めてくれば起こらない。よって、「バグありのコード」を修正すると次のようになる。

  • 「バグありのコード」修正版(SELECTの対象が変化)
 @students = Student.find_by_sql(<<-SQL)
      SELECT     students.*
      FROM       students
      LEFT JOIN  interviews ON students.interview_id = interviews.id
      ORDER BY   CASE WHEN held_date_time IS NULL
                   THEN DATE_ADD(NOW(),INTERVAL 1 YEAR)
                   ELSE held_date_time
                 END
    SQL

追記:MySQLの場合「COALESCE」を使うとスマート

リスト内の最初の非 NULL 要素を返す。

SELECT COALESCE(NULL,1);
 -> 1
SELECT COALESCE(NULL,NULL,NULL);
 -> NULL


これを使うと、上記のコードが以下のようになる。find_by_sql使わなくてもいいね。さすれば、「Railsで使う際の注意(find_by_sqlの注意かな)」も気にする必要がなくなるし。

Student.find(
              :all, 
              :include=>:interview, 
              :order=>”COALESCE(held_date_time, DATE_ADD(NOW(),INTERVAL 1 YEAR))”
            )


舞波」さんコメントありがとうございます☆

追々記:もっとスマートにできる

id:MeijiKさんのコメントより。

SELECT students.id,held_date_time,
FROM students
LEFT JOIN interviews ON students.interview_id = interviews.id
ORDER BY held_date_time IS NULL, held_date_time
;


MySQLのリファレンスを読んだところ、「IS NULL関数」は、真偽を「0/1」で返すので、それをソート基準に使えるということです。
よって、最初のソートで、held_date_timeがNULLでないレコードが上から並び、それ以降にNULLのレコードが並びます。
次に、held_date_timeでソートをかければ、held_date_timeがNULLのグループ内で(これはソートされないけど)、held_date_timeがNULLでないグループ内でheld_date_time基準のソートがかかり、held_date_timeがNULLでないレコードがきちんとソートされるという寸法です。
この発想はなかったです。MeijiKさん++です(*´艸`)