CODEWITHSUNDEEP
mysql
santa
santa

Reputation: 12512

Conditional ORDER BY

I have a simple query. Something like this:

SELECT
    id,
    item,
    action,
    desc,
    time
FROM t1
WHERE bus = 6
ORDER BY action DESC, time ASC

I need to modify ORDER BY part and only use action DESC if desc == ''

Basically it will output results and sort them by time in ASC order and if there are any actions that do not have description will be pegged at the top. Is it possible to do in a single query? If yes, how do I incorporate it?

Upvotes: 1

Views: 1636

Answers (2)

smozgur
smozgur

Reputation: 1812

Single query with one fake sort field.

SELECT
    `id`,
    `item`,
    `action`,
    `desc`,
    `time`
FROM t1
WHERE `bus` = 6
ORDER BY IF(`desc` = '', 0, 1), `action` DESC, `time` ASC

EDIT: Noticed that OP sql contains reserved word and I repeated it in my answer by assuming it is just sample - my bad. I added the backticks for future readers.

Upvotes: 1

Donnie
Donnie

Reputation: 46923

The case statement is super-useful and lets you do all sorts of fun things. Works everywhere a single column works.

SELECT
    id,
    item,
    action,
    t1.`desc`,
    time
FROM t1
WHERE bus = 6
ORDER BY 
  case t1.`desc` when '' then action else '' end DESC, 
  time ASC

Upvotes: 4

Related Questions