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!
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!
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?
ASKER
first!
07 July 2012
14 July 2012
18 July 2012
05 July 2012
01 July 2012
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.
ASKER
How should i do something like that Ray?
ASKER
could the use of CASE help?
or is it possible to combine 2 SELECT statements in 1 query?
or is it possible to combine 2 SELECT statements in 1 query?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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..
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..
ASKER
This did the trick (below EDIT:):
http://stackoverflow.com/q uestions/7 986455/php -mysql-uni on-order-b y
Thanks for the help! UNION did the trick!
http://stackoverflow.com/q
Thanks for the help! UNION did the trick!