Ordering By Date Based on Today

[aviddv1]

I'm trying to order a set of records with a field called "date" so that any records where date is >= now() will show first and be in ascending order.  Any records where date< now() will show after the first set and be in descending order.

so if today is 10/05/2006 it would look something like this:
10/05/2006
10/15/2006
10/31/2006
10/04/2006
9/28/2006
9/15/2006
9/01/2006

Any ideas?

Thanks.

[fenway]

I'm assuming you're storing these in a proper DATE column type... you could probably use "ORDER BY yourDate >= NOW(), yourDate ASC" if you didn't want the direction to change.  But since you do, you can't really do this with a single query... not in any way that I can think of right now, at least.  Of course, a UNION would do the trick, search you could sort each result set differently.  Not sure if that suits your needs.

[GeoffOs]

You could write two queries and union the results together
[url=http://dev.mysql.com/doc/refman/5.0/en/union.html]http://dev.mysql.com/doc/refman/5.0/en/union.html[/url]
For Example
[code]
select date from my table where date >= now() order by date desc
UNION ALL
select date from my table where date <= now() order by date asc
[/code]
Or something like that

[fenway]

Strictly speaking, you should only have the equality on one of the queries; also, remember that this won't be query-cached if you use NOW(), and since this will return the same result for 24hr, you may want to pass the current date from you as a string.  Just my $0.02.

[shoz]

First let me say that I also recommend that you use the UNION. Having said that, this query should also give the same results.
[code]
SELECT
table.*, IF(date >= '$date', 0, 1) AS d1, IF(date >= '$date', date, 1) AS d2
FROM
table
ORDER BY
d1 ASC, d2 ASC, date DESC
[/code]

EDIT:
The following should also work if you're not using dates before 1970.
[code]
SELECT
table.*, IF(date >= '$date', 0, 1) AS d1,
IF(date >= '$date', UNIX_TIMESTAMP(date),0-UNIX_TIMESTAMP(date)) AS d2
FROM
table
ORDER BY
d1 ASC, d2 ASC
[/code]

[fenway]

Interesting workaround... I always seem to ignore the possibility of using such expressions to simplify ordering... and I probably shouldn't.  I'm guessing that this single query might be faster, though perhaps a little less obvious.

[shoz]

I assumed that the UNION would order the results based on each individual query, but it does not.

[quote=http://dev.mysql.com/doc/refman/5.0/en/union.html]
Use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows.
[/quote]

The manual gives some examples of how you can handle this, but they are similar to the example I posted. You can of course run 2 separate queries, storing the result in order.

[fenway]

No kidding... makes sense, I wonder why I've never encountered that before... oh, now I remember.  If usually use ORDER BY with LIMIT, so it "works".  If you knew how many rows there were, or just used a huge number, you could fake the limit, and I'll bet the order by would still catch.