Mysql order of sorting

Question

I have 1 table which having columns id, name and threat & want to use order by clause for threat column.

Threat 1 is highest priority and 5 is lowest one. Few records won't have threat score and having Null as score.

Hence on displaying it on screen, subtracting the threat score with numeric value 6 (if threat is having value) and same will be used as threat score.

On sorting the column threat by asc, it picking first null values (no threat) and sorting by desc, its picking first value as 5 (lowest threat).

Hence final order on screen is (after subtracting by 6) for ASC. If you see here, threat score with 5 showing as top because SQL sending record sorted by threat score.

+-------------+------------+
| screen data | Table data |
+-------------+------------+
|    NULL     |     Null   |
|     1       |      5     |
|     2       |      4     |
|     3       |      3     |
|     4       |      2     |
|     0       |      0     |
+-------------+------------+

Already tried ORDER BY threat * -1 ASC but its giving error

BigInt unsigned error out of range

Here my requirement is get threat score with 1 as top record while sorting the records because NULL is having precedents over it.

Expected behavior for sort (ASC or desc)

+--------+
| Threat | 
+--------+
|   1    |
|   2    |
|   3    |
|   4    |
|   5    |
|   0    |
|  NULL  |
+--------+

--Working Solution--

With help of awesome people here, below solution worked like charm.

ORDER BY IF (threat=0,6, ifnull(threat,6)) ASC ;

The solution provided in the link will help you out [MySQL Orderby a number, Nulls last](https://stackoverflow.com/a/4195311/705479) — sadia, Jun 11 '19 at 05:25
From [the documentation](https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html): *When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC.* — Shawn, Jun 11 '19 at 05:29
You could try using `ORDER BY IFNULL(threat,'z') ASC` or `ORDER BY ISNULL(threat) ASC`.. — FanoFN, Jun 11 '19 at 05:34

mostafa8026 · Answer 1 · 2019-06-11T11:44:15.183

You have to handle NULL values in this cases. there is few ways to handle NULL values. you can change the default value to some number which is appropriate for your use, in this case, NULL values will be disappeared and your problem will be solved. another solution is to have NULL values but handle them when you query the table. you can use IFNULL(expression, alt_value), in your case it will be:

IFNULL(threat, 6)

If the schema is:

CREATE TABLE IF NOT EXISTS `threat_table` (
  `id` int(3) unsigned ,
  `name` varchar(200),
  `threat` int(3) unsigned
) DEFAULT CHARSET=utf8;

INSERT INTO `threat_table` (`id`, `name`, `threat`) VALUES
  ('1', 'test1', '5'),
  ('2', 'test2', NULL),
  ('3', 'test3', '4'),
  ('4', 'test4', '3'),
  ('5', 'test5', '2'),
  ('6', 'test6', '1');

and your query is:

SELECT * from table order by IFNULL(threat, 6)

then the result is satisfactory:

+----+-------+--------+
| id | name  | threat |
+----+-------+--------+
|  6 | test6 | 1      |
|  5 | test5 | 2      |
|  4 | test4 | 3      |
|  3 | test3 | 4      |
|  1 | test1 | 5      |
|  2 | test2 | (null) |
+----+-------+--------+

take a look at this fiddle.

Update:

If the threat number can be any value othe than just [1..5], we can use this query:

select * from threat_table order by IFNULL(threat, ~0 >> 33)

@EternalSunShine What if `threat` has value `7` inserted ..? — Barbaros Özhan, Jun 11 '19 at 06:50
In the question, we have threat from 1 to 5. if the threat can accept any number, we can use maximum int nubmber => `SELECT ~0 >> 33 as max_int_signed` and the query change to `select * from threat_table order by IFNULL(threat, ~0 >> 33)` — mostafa8026, Jun 11 '19 at 11:41

score 1 · Answer 2 · edited Jun 11 '19 at 05:14

1

Please use ifnull() function. Replace null value with a suitable value in select query.

edited Jun 11 '19 at 05:14

Arulkumar

12,966
14
47
68

answered Jun 11 '19 at 05:10

Swarnendu Sarkar

9
3

score 1 · Answer 3 · answered Jun 11 '19 at 05:38

1

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

I hope this works.

answered Jun 11 '19 at 05:38

Twini

195
16

Barbaros Özhan · Answer 4 · 2019-06-11T05:30:36.703

0

try to cast your value to signed

order by cast(-1 *coalesce(Threat,0) as signed)

and coalesce(Threat,0) conversion stands for providing the null value appears at the bottom.

edited Jun 11 '19 at 05:30

answered Jun 11 '19 at 05:13

Barbaros Özhan

59,113
10
31
55

score 0 · Answer 5 · answered Jun 11 '19 at 05:20

0

ORDER BY column_name IS NULL, column_name ASC

you can also use mysql COALESCE function See the documentation here

answered Jun 11 '19 at 05:20

Parvej Ahammad

45
1
8

Mysql order of sorting

--Working Solution--

5 Answers5