CODEWITHSUNDEEP
mysqlnull
amaster
amaster

Reputation: 2163

How to Order By with NULL in middle with MySQL

Here is the sqlfiddle for this question.

I have a students table similar to

student_id | first_name | doing_well
-----------+------------+-----------
1          | John       | NULL
2          | Jane       | NULL
3          | Steve      | 0
4          | Sue        | 1
5          | James      | 0

What I am trying to do is order the students according to the doing_well field. The students doing well 1 should be on top, the students not doing well 0 should be on bottom, and the students with unknown doing well status NULL should be in the middle.

I have tried using ORDER BY FIELD() with no success

SELECT
  *
FROM
  students
ORDER BY
  FIELD(doing_well,'0',NULL,'1')

I would really like to accomplish this and keep the undefined as NULL and not have to change the undefined to a value.

The end result should be like

4  | Sue   | 1
1  | John  | NULL
2  | Jane  | NULL
3  | Steve | 0
5  | James | 0

Alternatively, Answers that can do the above using PHP to sort after the query are welcome but not preferred.

Upvotes: 1

Views: 475

Answers (2)

echo_Me
echo_Me

Reputation: 37233

you can also do this

    ORDER BY
    IFNULL(doing_well, 0.5) desc

DEMO

Upvotes: 4

user330315
user330315

Reputation: 

order by 
    case 
      when doing_well = 1 then 0
      when doing_well is null then 1
      else 2
    end;

Upvotes: 7

Related Questions