CODEWITHSUNDEEP
mysqlsql-order-by
Norto23
Norto23

Reputation: 2269

Different ORDER BY direction for MySql query results

I am trying to do some ordering on a mysql query that I can't figure out. 

id | status | created_at
------------------------
 1 | open   | 1348778070
 2 | closed | 1348711241
 3 | open   | 1348839204
 4 | closed | 1348738073
 5 | banned | 1348238422

How do I order the above table so that the 'open' records are first, in ASC order; and then the non-open records are second in DESC order? In another word, have a dynamic second level ordering direction based on some condition?

I have tried a UNION of two SELECT queries with ordering within them, which doesn't work because UNION by default produces an unordered set of rows.

Also I've tried a pseudo column that subtracts the created_at timestamp from a large number, for the closed status records, so I can just ORDER BY ASC to get the result as per below...

SELECT table.*, (table.created_at) as tmp_order FROM table
  WHERE table.status = 'open'
UNION
  SELECT table.*, (999999999 - table.created_at) as tmp_order FROM table
WHERE table.status = 'closed'
ORDER BY tmp_order ASC

This works but I feel there has to be a better way. Ideally a solution would not include a random big number as above

Upvotes: 4

Views: 3627

Answers (4)

peterm
peterm

Reputation: 92785

UPDATED

SELECT *
  FROM tmp_order
 ORDER BY FIELD(status, 'open') DESC, 
          CASE 
            WHEN status = 'open' 
              THEN created_at 
              ELSE (999999999 - created_at) 
          END

or 

SELECT *
  FROM tmp_order
 ORDER BY FIELD(status, 'open') DESC, 
          CASE 
            WHEN status = 'open' 
              THEN created_at END,
          CASE 
            WHEN status <> 'open' 
              THEN created_at END DESC

Output:

| ID | STATUS | CREATED_AT |
----------------------------
|  1 |   open | 1348778070 |
|  3 |   open | 1348839204 |
|  4 | closed | 1348738073 |
|  2 | closed | 1348711241 |
|  5 | banned | 1348238422 |

Here is SQLFiddle demo.

Upvotes: 5

Bohemian
Bohemian

Reputation: 424983

In your case, you can probably do it in one query, but the general solution, which works for any two different and unrelated. orderings is to use two unioned subqueries each with their own ordering;

SELECT * FROM (
   SELECT *
  FROM table
  WHERE table.status = 'open'
  ORDER BY created_at DESC) x
UNION ALL
SELECT * FROM (
  SELECT *
  FROM table
  WHERE table.status = 'closed'
  ORDER BY created_at) y

Upvotes: 0

fthiella
fthiella

Reputation: 49049

This is how I would solve it:

SELECT
  id, status, created_at
FROM
  yourtable
ORDER BY
  status DESC,
  CASE WHEN status='open'   THEN created_at END,
  CASE WHEN status='closed' THEN created_at END DESC

Upvotes: 1

Nir Alfasi
Nir Alfasi

Reputation: 53525

Try:

SELECT id, status, 
if (status = 'open', created_at, 999999999 - created_at) as tmp_order 
FROM table 
ORDER BY status, tmp_order

Upvotes: 1

Related Questions