Jump to content

Ordering By Date Based on Today


aviddv1

Recommended Posts

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.