Skip navigation links

MySQL Forums :: InnoDB :: Mysql order by if condition - wrong order


Advanced Search

Mysql order by if condition - wrong order
Posted by: Jan Kowalski ()
Date: August 22, 2013 07:55AM

Hi everybody, I have a problem with one of my queries.
I got a simple table:

CREATE TABLE `dev_a4a`.`test` ( `id` INT UNSIGNED NOT NULL
AUTO_INCREMENT , `type` VARCHAR(45) NOT NULL , `priority` INT NOT
NULL , PRIMARY KEY (`id`) );

with following rows:

id|type |priority
1 |long |8
2 |long |3
3 |short|9
4 |short|1

I want to get rows ordered by condition:

SELECT (RAND() * priority) as prio, type, priority FROM test
ORDER BY (IF(type = 'short', '2', prio)) DESC, id DESC

As a result I got rows not ordered by if condition. Every time it looks like it's random. Here's one of possible results:

prio | type | priority
'0.05013570194145264', 'long', '8'
'2.9015473750434326', 'long', '3'
'0.320064320527077', 'short', '1'
'7.598900996706356', 'short', '9'

What am I doing wrong?

Expected result:

prio | type | priority
'2.9015473750434326', 'long', '3' <- order by prio
'7.598900996706356', 'short', '9' <- order by common value 2
'0.320064320527077', 'short', '1' <- order by common value 2
'0.05013570194145264', 'long', '8' <- order by prio


------------- EDIT ------------

Ok problem solved:

SELECT CASE WHEN type = 'short' THEN 2 ELSE RAND() * priority END AS prio, type, priority FROM test ORDER BY 1 DESC, id DESC



Edited 1 time(s). Last edit at 08/23/2013 07:25AM by Jan Kowalski.

Options: ReplyQuote


Subject Views Written By Posted
Mysql order by if condition - wrong order 2012 Jan Kowalski 08/22/2013 07:55AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.