CODEWITHSUNDEEP
mysql
Chan
Chan

Reputation: 1959

Use IF statement on ORDER BY

Table Structure

id int unsigned auto_increment
type int
value varchar

Data

id  type value
1   1   1000000
2   1   20000
3   1   25000
4   1   50000
5   1   25000
6   1   20000
7   1   20000
8   1   50000
9   1   20000
10  1   50000
11  1   50000
12  1   50000
13  1   200000
14  1   20000
15  1   25000
16  1   20000
17  1   25000
18  1   25000
19  1   20000
20  1   50
21  1   20000
22  1   50000
23  1   1000000
24  1   25000
25  1   25000
26  1   50000
27  1   50000
28  1   50000
29  2   a
30  2   b

There are two types in the table, if type is 1 value will be number, if type is 2 the value will be string, therefore, If I want to order by value I need to convert value to UNSIGNED when type is 1, just order by the letter if type is 2, here is the MySQL script I wrote.

SELECT *,
    CONVERT(`value`, UNSIGNED) `result`
FROM `tests`
ORDER BY IF(`result` = 0, `value`, `result`) DESC

I use result = 0 to check if the value is string or number, if result = 0 ORDER BY value directly, if not ORDER BY CONVERT result, but the output is not exactly order by the CONVERT result, if I edit the code to

SELECT *,
    CONVERT(`value`, UNSIGNED) `result`
FROM `tests`
ORDER BY IF(`result` = 0, `result`, `result`) DESC

The output will be correct, that's mean IF condition is work but the result is wrong, have no idea about that.

Dump Query

CREATE TABLE IF NOT EXISTS `tests` (
  `id` int(10) unsigned NOT NULL,
  `type` int(11) DEFAULT '1',
  `value` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tests` (`id`, `type`, `value`) VALUES
(1, 1, '1000000'),
(2, 1, '20000'),
(3, 1, '25000'),
(4, 1, '50000'),
(5, 1, '25000'),
(6, 1, '20000'),
(7, 1, '20000'),
(8, 1, '50000'),
(9, 1, '20000'),
(10, 1, '50000'),
(11, 1, '50000'),
(12, 1, '50000'),
(13, 1, '200000'),
(14, 1, '20000'),
(15, 1, '25000'),
(16, 1, '20000'),
(17, 1, '25000'),
(18, 1, '25000'),
(19, 1, '20000'),
(20, 1, '50'),
(21, 1, '20000'),
(22, 1, '50000'),
(23, 1, '1000000'),
(24, 1, '25000'),
(25, 1, '25000'),
(26, 1, '50000'),
(27, 1, '50000'),
(28, 1, '50000'),
(29, 2, 'a'),
(30, 2, 'b');

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

Use multiple clauses in the order by:

ORDER BY type,  -- put numbers first
         (case when type = 1 then value + 0 end) desc, -- convert values to numbers
         value

The problem with using one expression, such as if() or case, is that the result is converted to one type. So, you are sorting either numbers or strings, but not both at the same time.

Upvotes: 2

Related Questions