Jump to content


Photo

Ordering By Date Based on Today


  • Please log in to reply
7 replies to this topic

#1 aviddv1

aviddv1
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 05 October 2006 - 04:31 PM

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.

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 05 October 2006 - 07:11 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 GeoffOs

GeoffOs
  • Members
  • PipPip
  • Member
  • 24 posts
  • LocationCheshire, England

Posted 05 October 2006 - 07:14 PM

You could write two queries and union the results together
http://dev.mysql.com...0/en/union.html
For Example
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
Or something like that
Beyond a critical point within a finite space, freedom diminishes as numbers increase....[br]The human question is not how many can possibly survive within the system, but what kind of existence is possible for those who do survive."[br]-- Frank Herbert - Dune

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 05 October 2006 - 07:17 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 05 October 2006 - 10:28 PM

First let me say that I also recommend that you use the UNION. Having said that, this query should also give the same results.
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

EDIT:
The following should also work if you're not using dates before 1970.
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


#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 October 2006 - 01:10 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 06 October 2006 - 01:44 PM

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

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.


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.

#8 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 06 October 2006 - 03:17 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users