MySQL: How to use IF condition for ORDER

Question

Possible Duplicate:
Can you add “IF” statement in PHP MYSQL ORDER BY?

How can I use IF condition for ORDER in MySQL?

For instance, my query below that returns an error,

SELECT *
FROM page AS p

WHERE p.parent_id != p.page_id
AND p.type = 'post'
AND p.parent_id = '7'


IF(
    'date created' = 'date created', 
    ORDER BY p.created_on DESC,
    ORDER BY p.created_on ASC
)

message,

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF( 'date created' = 'date created', ORDER BY p.created_on DESC, ORDER BY p.' at line 17

The first 'date created' is variable. So if 'date created' = 'date created',

then ORDER BY p.created_on DESC

else ORDER BY p.created_on ASC

found the answer here: http://stackoverflow.com/questions/3550942/can-you-add-if-statement-in-php-mysql-order-by — Run, Apr 19 '12 at 03:18
Your question is ambiguous, I got an impression that you are asking how to dynamically order the direction, as we can see from your question, both condition's true and false is matched with created_on and they differ only on sort direction(DESC vs ASC). Next time, make your question free from ambiguity — Michael Buen, Apr 19 '12 at 14:08

Michael Buen · Answer 1 · 2012-04-19T02:01:56.717

Use this:

create table person
(
  name varchar(50)
);


insert into person(name)
select 'John' union
select 'Paul' union
select 'George' union
select 'Ringo' ;



set @direction = 1;

-- the '' is ignored on sorting since they are all the same values
select * from person
order by 
    IF (@direction = 0, name,'') ASC,
    IF (@direction = 1, name,'') DESC

Live test: http://www.sqlfiddle.com/#!2/22ea1/1

Another approach is to use -1 for descending direction, and +1 for ascending direction, then multiply it to the field, works only on number fields only:

create table person
(
  name varchar(50), birth_year int
  );


insert into person(name, birth_year)
select 'John', 1940 union
select 'Paul', 1941 union
select 'George', 1943 union
select 'Ringo', 1940 ;


set @direction = -1;  -- -1: descending, 1: ascending

select * from person
order by birth_year * @direction

Live test: http://www.sqlfiddle.com/#!2/f78f3/3

MySQL: How to use IF condition for ORDER

1 Answers1