In Order by Date field starting at the midpoint of the date range
I have table "A" with field "date". I want to make a select query and order the rows with previous dates in descending order and then rows with the next dates in ascending order, all in one query. Is it possible?
For example, table "A":
id date
---------------------
a march-20
b march-21
c march-22
d march-23
e march-24
I would like to get, given the start date "march-22", this result:
id date
---------------------
c march-22
b march-21
a march-20
d march-23
e march-24
In one request, because I am doing it with two of them and it is slow because the only difference is in the sorting and the joins I need to make are a little "heavy".
Many thanks.
You can use something like this -
SELECT *
FROM test
ORDER BY IF(
date <= '2012-03-22',
DATEDIFF('2000-01-01', date),
DATEDIFF(date, '2000-01-01')
);
Here is a link to the test on the SQL Fiddle - http://sqlfiddle.com/#!2/31a3f/13
source to share
This is wrong, sorry :( From the documentation:
However, using ORDER BY for individual SELECT statements means nothing about the order in which rows appear in the final result, because UNION creates an unordered set of rows by default. Therefore, the use of ORDER BY in this context is usually associated with LIMIT, so it is used to define a subset of the selected rows to retrieve for a SELECT, although this does not necessarily affect the order of those rows in the final UNION result. If ORDER BY appears without LIMIT in the SELECT, it is optimized because it will have no effect.
This should do the trick. I'm not 100% sure about adding an order to UNION ...
SELECT * FROM A where date <= now() ORDER BY date DESC
UNION SELECT * FROM A where date > now() ORDER BY date ASC
source to share
I think the real question is how to do the join once. Create a temporary table with the join result and make 2 selections from that table. So it will only take a long time to create (once) not on the selected query (twice).
CREATE TABLE tmp SELECT ... JOIN -- do the heavy duty here
With this, you can make two select statenets as you originally did.
source to share