I need unusual ordering mysql results

Question

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)

the year isnt useful in my specific case, these are birthdays, so the year doesnt matter — mrpatg, Apr 18 '10 at 17:50
You don't say *how* it doesn't work. Can you give an example of some rows that don't sort how you expect them to? It would help if you show what the result of the query is, and how that is different from what you expected. — Bill Karwin, Apr 18 '10 at 17:56
well what's wrong with the order you get? And why do you have separate bdays table? — Your Common Sense, Apr 18 '10 at 17:58
it sorts the birthdays starting january, but i want them sorted asc from the current date — mrpatg, Apr 18 '10 at 18:00
omg it being sorted ASC! and if you want it to be filtered, add a WHERE condition, don't think mysql will telepathically understand your *wishes* — Your Common Sense, Apr 18 '10 at 18:08
i gathered that much...which is why i need help understanding how to accomplish this...sans the telepathy. — mrpatg, Apr 18 '10 at 18:11

score 4 · Answer 1 · answered Apr 18 '10 at 18:27

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 |
+----+------------+------+

score 2 · Accepted Answer · answered Apr 18 '10 at 18:43

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.

score 1 · Answer 3 · answered Apr 18 '10 at 18:11

1

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

answered Apr 18 '10 at 18:11

Your Common Sense

156,878
40
214
345

score 1 · Answer 4 · answered Apr 18 '10 at 18:18

1

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

answered Apr 18 '10 at 18:18

dar7yl

3,727
25
20

I need unusual ordering mysql results

4 Answers4

Linked