MySql if then query

Question

Is it possible to use if/then statements in mySql? I am looking to check the value of a column in a JOIN table and order the results based on the value of that column.

My sorry attempt:

SELECT t1.*
FROM t1 
JOIN t2 ON t1.ID = t2.ID
WHERE t1.ID = '888'
if(t2.col1 = '1') ORDER BY t1.tid ASC
else ORDER BY RAND()

derobert · Accepted Answer · 2011-01-06 21:09:09Z

1

You can use CASE:

ORDER BY
  CASE WHEN t2.col1 = 1 THEN t1.tid ELSE rand() END ASC

Beware the performance of this may not be so good, as MySQL won't be able to use an index for the order t1.tid.

You should also be aware that you can order by multiple things, so that possibly this will do what you want:

ORDER BY t1.tid ASC, RAND()

Also, you should keep in mind that ORDER BY RAND() LIMIT 1 will actually fetch every row, calculate a random number for each, sort them, then finally just return the first row. So on data of a reasonable size, it will be slow (and also result in temp tables).

edited Jan 6, 2011 at 21:09

answered Jan 6, 2011 at 21:01

derobert

51.4k15 gold badges98 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySql if then query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related