How to get NULL at the top of the sort?

Question

Two tables are defined:

CREATE TABLE `users` (
  `user_id` mediumint(6) unsigned NOT NULL AUTO_INCREMENT,
  `score` tinyint(1) unsigned DEFAULT NULL,
  PRIMARY KEY (`user_id`)
);

CREATE TABLE `online` (
  `user_id` mediumint(6) unsigned NOT NULL AUTO_INCREMENT,
  `url` varchar(255) NOT NULL,
  PRIMARY KEY (`user_id`)
);

How to combine the tables so that the result would be sorted by the score field from the largest to the smallest but at the top there were records with the value NULL? This query does not sort the second sample:

(SELECT * FROM `online` JOIN `users` USING(`user_id`) WHERE `score` IS NULL)
UNION
(SELECT * FROM `online` JOIN `users` USING(`user_id`) WHERE `score` IS NOT NULL ORDER BY `score` DESC)

Possible duplicate of [mysql order by, null first, and DESC after](https://stackoverflow.com/questions/9307613/mysql-order-by-null-first-and-desc-after) — Ivar, Jun 21 '19 at 11:35

Gordon Linoff · Accepted Answer · 2019-06-22T03:37:32.287

3

Use two keys in the sort:

SELECT *
FROM `online` o JOIN
     `users`
     USING (user_id)
ORDER BY (`score` IS NULL) DESC, Score DESC;

MySQL treats booleans as numbers in a numeric context, with "1" for true and "0" for false. So, DESC puts the true values first.

Incidentally, your version would look like it works if you used UNION ALL rather than UNION. However, it is not guaranteed that the results are in any particular order unless you explicitly have an ORDER BY.

The UNION incurs overhead for removing duplicates and in doing so rearranges the data.

edited Jun 22 '19 at 03:37

answered Jun 21 '19 at 11:35

Gordon Linoff

1,242,037
58
646
786

Thanks for you reply! Seems for USING need brackets? – Dmitriy Jun 21 '19 at 12:21

score 1 · Answer 2 · answered Jun 21 '19 at 11:53

1

Try:

select * from online join users using (user_id) order by ifnull(score, 10) desc;

answered Jun 21 '19 at 11:53

Rafael Segovia

247
3
11

Fahmi · Answer 3 · 2019-06-21T11:40:28.060

0

You can try below -

select * from
(
SELECT *,1 as ord FROM `online` JOIN `users` USING(`user_id`) WHERE `score` IS NULL
UNION
SELECT *,2  FROM `online` JOIN `users` USING(`user_id`) WHERE `score` IS NOT NULL
)A ORDER BY ord asc,`score` DESC

edited Jun 21 '19 at 11:40

answered Jun 21 '19 at 11:35

Fahmi

37,315
5
22
31

score 0 · Answer 4 · answered Jun 21 '19 at 11:36

0

You can use order by Nulls Last in the end of your sql to show nulls on the first.

answered Jun 21 '19 at 11:36

Yogesh

47
1
10

How to get NULL at the top of the sort?

4 Answers4