I need unusual mysql orders

Question

Im trying to sort the results of the entry from the current date

this is what i am 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, sorting by date now, sorts birthdays starting in January

what I need, instead of starting the list in January, starts it from the current date.

So, instead of:

 January February March April May June July August September November December 

He will order them as follows:

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

Answer 1

You may try:

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

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

Note This is similar to the Col method. Shrapnel

Answer 2

Here's how I would do it:

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

The date format %j is the day of the year, that is, the number 001 ... 366.

I tested this on some sample data, and it sorts the way you describe: it ignores the year and sorts the next date that falls after the current date first, then goes back and wraps up to dates earlier this 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 | +----+------------+------+ 

Answer 3

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

Answer 4

you may try:

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