CODEWITHSUNDEEP
mysqlsortingrecord
Neeraj
Neeraj

Reputation: 9175

Sort Records and Get Non Null Records at Top

I have a table "users" having data like

id   firstname  lastname
1    NULL       NULL
2    NULL       NULL
3    Tim        Kanter
4    John       Mathews
5    Brady      Allen

I need to get the records order by firstname but non null records should come first in ascending order. Therefore, How can i get the output like

id   firstname  lastname
5    Brady      Allen
4    John       Mathews
3    Tim        Kanter
1    NULL       NULL
2    NULL       NULL

Upvotes: 1

Views: 32

Answers (2)

Raging Bull
Raging Bull

Reputation: 18747

Try this:

SELECT * FROM TableName 
ORDER BY CASE WHEN firstname IS NULL THEN 1 ELSE 0 END, firstname

Result:

ID  FIRSTNAME   LASTNAME
5   Brady       Allen
4   John        Mathews
3   Tim         Kanter
1   (null)      (null)
2   (null)      (null)

See result in SQL Fiddle.

Upvotes: 2

Uriil
Uriil

Reputation: 12628

You can do it using UNION:

  SELECT * FROM (SELECT * FROM table WHERE firstname IS NOT null
  ORDER BY firstname) AS `t1`
  UNION
  SELECT * FROM table WHERE firstname IS  null

Upvotes: 0

Related Questions