CODEWITHSUNDEEP
phpmysqltimetimestamp
hashi
hashi

Reputation: 77

MySQL multidirectional ORDER BY

I have a table with the column 'time' which stores timestamps. I want to order it so that if the timestamp is in the future (ie. greater than what PHP's time() function returns), it orders it ascending. If it is not in the future, order it descending in value.

EDIT: Suppose I had this list of timestamps (I know they're not actual ones, but for simplicity's sake):

1896
779
468
1478
846
1384

Then supppose the current timestamp is '1000'. The list should be reordered by the query to look something like this:

1384
1478
1896
846
779
468

So effectively all the data above 1000 is sorted ASC, and all data below is sorted DESC.

Upvotes: 2

Views: 64

Answers (2)

mishu
mishu

Reputation: 5397

I don't think that you can sort the same query by the same columns in two different ways, but the same result might be achieved by doing this in two steps. You can either run two separate queries and merge the results or another option would be to use an UNION

Let's say you now have:

SELECT * FROM `table` ORDER BY `timestamp`

You can rewrite it as:

(
    SELECT 
        * 
    FROM 
        `table` 
    ORDER BY 
        `timestamp` DESC 
    WHERE 
        timestamp < NOW()
) 
UNION ALL 
(
    SELECT 
        * 
    FROM 
        `table` 
    ORDER BY 
        `timestamp` ASC 
    WHERE timestamp >= NOW()
)

Upvotes: 2

Arth
Arth

Reputation: 13110

You can put an expression in the ORDER BY:

 ORDER BY ts >= NOW() DESC, (ts >= NOW()) * ts ASC, ts DESC

Booleans conditions are returned as 0 or 1 in MySQL.

Thus all the future timestamps are ordered 1 DESC, timestamp ASC, [timestamp DESC] and the past timestamps are ordered 0 DESC, 0 ASC, timestamp DESC and you get the desired order.

This would save you repeating predicates, but would probably hamper performance.

Upvotes: 3

Related Questions