CODEWITHSUNDEEP
mysql
superUntitled
superUntitled

Reputation: 22527

MySql if then query

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()

Upvotes: 1

Views: 660

Answers (1)

derobert
derobert

Reputation: 51137

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).

Upvotes: 1

Related Questions