CODEWITHSUNDEEP
mysqlsqlsql-order-by
EternalSunShine
EternalSunShine

Reputation: 409

Mysql order of sorting

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 ;

Upvotes: 0

Views: 136

Answers (5)

mostafa8026
mostafa8026

Reputation: 313

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)

Upvotes: 2

Twini
Twini

Reputation: 195

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

I hope this works.

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65158

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.

Upvotes: 0

Parvej Ahammad
Parvej Ahammad

Reputation: 45

ORDER BY column_name IS NULL, column_name ASC

you can also use mysql COALESCE function See the documentation here

Upvotes: 0

Swarnendu Sarkar
Swarnendu Sarkar

Reputation: 9

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

Upvotes: 1

Related Questions