CODEWITHSUNDEEP
phpmysql
mrpatg
mrpatg

Reputation: 10117

I need unusual ordering mysql results

Im trying to order results ASCENDING from the current date

this is what im using now;

SELECT * FROM friends JOIN bdays 
ON bdays.user = friends.friendname 
WHERE username = $userid ORDER BY DATE_FORMAT(date, '%m %d')

any ideas?

example ordering by date now, sorts the birthdays starting at january

what i need, is instead of starting the list at january, is starting it from the current date.

So, instead of;

January
February
March
April
May
June
July
August
September
November
December

It will order them like this;

April (current month/day)
May
June
July
August
September
November
December
January
February
March
April (all the way up to yesterday)

Upvotes: 4

Views: 277

Answers (4)

cmptrgeekken
cmptrgeekken

Reputation: 8092

You could try:

ORDER BY
   DATE_FORMAT(date,'%m %d') < DATE_FORMAT(NOW(),"%m %d"),
   DATE_FORMAT(date,'%m %d');

First, order by whether or not the date is less than the current date, then order by month and date in ascending order.

NOTE This looks like the method Col. Shrapnel was referring to.

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 563011

Here's how I'd do it:

SELECT *, (DATE_FORMAT(date, '%j')-DATE_FORMAT(NOW(), '%j')+365)%365 AS d 
FROM foo ORDER BY d;

The %j date format is the day of the year, i.e. a number 001...366.

I tested this on some sample data and it sorts in the way you describe: it ignores the year, and sorts the next date that falls after the current date first, then ascending, and wrapping around to dates earlier in the year.

+----+------------+------+
| id | date       | d    |
+----+------------+------+
|  5 | 1999-05-15 |   27 |
|  6 | 1992-06-15 |   59 |
|  7 | 1990-07-15 |   88 |
|  8 | 1988-08-15 |  120 |
|  9 | 1980-11-15 |  212 |
|  1 | 2010-01-15 |  272 |
|  2 | 2009-02-15 |  303 |
|  3 | 2004-03-15 |  332 |
|  4 | 2002-04-15 |  362 |
+----+------------+------+

Upvotes: 4

dar7yl
dar7yl

Reputation: 3757

you might try:

SELECT *, DATE_FORMAT(date, '%m %d') as adate FROM friends JOIN bdays 
ON bdays.user = friends.friendname 
WHERE username = $userid 
ORDER BY adate

Upvotes: 1

Your Common Sense
Your Common Sense

Reputation: 158005

something like order by if(date_format(date,'%m%d') < date_format(now(),'%m%d')),1,0), date_format(date,'%m%d')

Upvotes: 1

Related Questions