CODEWITHSUNDEEP
mysql
puks1978
puks1978

Reputation: 3697

MySQL multiple order set

I have a query with an order

ORDER BY FIND_IN_SET(status, '1,5,3,4,2'), end_date ASC

but what I would like is any record with status 1 to be ordered end_date ASC and any status 2 ordered by end_date DESC and the others ordered by end_date ASC.

Can this be done? Thanks

Upvotes: 3

Views: 249

Answers (3)

coyotesqrl
coyotesqrl

Reputation: 162

Refining Devart's answer...

SELECT * FROM table_name
  ORDER BY find_in_set(status, '1,5,3,4,2')
  CASE
    WHEN (status = 2) then TO_DAYS(end_date) * -1
    ELSE TO_DAYS(end_date)
  END

Upvotes: 1

Devart
Devart

Reputation: 121902

Try this query -

SELECT * FROM table_name
  ORDER BY
    IF(status < 2, status, 3),
    IF(status = 2, TO_DAYS(end_date) * -1, TO_DAYS(end_date))

Upvotes: 2

Brent Baisley
Brent Baisley

Reputation: 12721

Use 2 queries to get your different sorts, then get the UNION of the queries.

SELECT * FROM (
(SELECT * FROM table WHERE status!=2 
 ORDER BY FIND_IN_SET(status, '1,5,3,4'), end_date ASC)
UNION
(SELECT * FROM table WHERE status=2 ORDER BY end_date DESC)
) AS table_sort ORDER BY FIND_IN_SET(status, '1,5,3,4,2')

Since you want the status 2 records last, technically you wouldn't need to SELECT on the UNION and ORDER it, but I added that in case you wanted a different sort order.

Upvotes: 0

Related Questions