Link to home
Start Free TrialLog in
Avatar of peps03
peps03

asked on

Sorting mysql query by 2 conditions

Hi,

I'm trying to sort a mysql query by 2 conditions: Deadline in future or deadline in past.

The table has a column named 'deadline'. When outputting the result i would like to first show all the deadlines in the future with the nearest first, when all these are outputted, the ones with a deadline in the past should be outputted, with the most recently 'expired' at the top.

Like this, with today being 7 July 2012:
14 July 2012
18 July 2012
05 July 2012
01 July 2012

the results are paginated, so i would like to be able to achieve this with 1 query if that is possible..

Many thanks!
Avatar of Neil Russell
Neil Russell
Flag of United Kingdom of Great Britain and Northern Ireland image

Just as an asside, what about the current day? You only mention dealines in the future and in the past. What happens to those who's deadline is Today?
Avatar of peps03
peps03

ASKER

first!
07 July 2012
14 July 2012
18 July 2012
05 July 2012
01 July 2012
The order is kind of counterintuitive if you want to achieve this in one query.  My guess is that you could use some kind of temporary table (engine=memory) and make two select queries to load the temporary table with the rows in the correct order.  Then you could make a select from the temporary table to get a single results set for pagination.
Avatar of peps03

ASKER

How should i do something like that Ray?
Avatar of peps03

ASKER

could the use of CASE help?
or is it possible to combine 2 SELECT statements in 1 query?
ASKER CERTIFIED SOLUTION
Avatar of hielo
hielo
Flag of Wallis and Futuna image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of peps03

ASKER

Thanks hielo!
That works, but the ordering is now not sorted by deadline.

Adding  this: ORDER BY deadline ASC to the end of the query sorts the entire query, putting the passed deadlines back on top again.

adding ORDER BY deadline ASC to both parts of the query results in an error..
Avatar of peps03

ASKER

This did the trick (below EDIT:):
http://stackoverflow.com/questions/7986455/php-mysql-union-order-by

Thanks for the help! UNION did the trick!