Sorting MySQL table by next due date

Question

Hi folks,
I have a MySQL table that stores (amongst other things) a persons date of birth.

Is there any way to sort the table so the next due birthday is at the top?

I have the date stored in standard yyyy-mm-dd DATE format and seperate year, month and day fields so I can sort it by the first birthday of the year (ORDER BY month, day) but I can't seem to get any further.

Any help would be greatly appreciated.
Zagga

Answer 1

There is no need to store separately the year, month, nor day.
And you don't need to order your table either.

All you've got to do is order your query as you'd like.
Something like this should get you started:

  SELECT month(t.dob) as m, 
         dayofmonth(t.dob) as d, 
    FROM mytable t 
ORDER BY 1,2

Answer 2

Select ...
From 'Table'
Where DayOfYear(BirthDate) <= DayOfYear(CurDate())
Order By DayOfYear(BirthDate) Desc

Because of the heavy use of functions in the Where and Order By clause this probably won't perform well.

EDIT David Gelhar has the right idea for the full list. Another way would be:

Select ...
From 'Table'
Where DayOfYear(BirthDate) <= DayOfYear(CurDate())
Order By Case
            When DayOfYear(BirthDate) <= DayOfYear(CurDate()) Then 0
            Else 1
            End ASC
    , DayOfYear(BirthDate)  DESC

Again, not the best performing query but it should give you the answer you want.

Answer 3

Interesting question, because to do it right you need to get the remaining birthdays for this year, then wrap around to next year. Something like:

Select ...
From 'Table'
Where DayOfYear(BirthDate) >= DayOfYear(CurDate())
Order By DayOfYear(BirthDate)
union
Select ...
From 'Table'
Where DayOfYear(BirthDate) < DayOfYear(CurDate())
Order By DayOfYear(BirthDate)

EDIT Thinking about this, you actually do need to use the broken-out "month" and "day" fields instead of just looking at DayOfYear; otherwise you'll get the wrong answer if this year is leap year and the person was born in a non-leap year (or vice-versa). That's because we think of our birthday as being "March 1st", not "the 60th day of the year".

And, of course, you can't use different "order by" clauses in the two halves of the UNION...

So, something more like:

Select 1,mon,day,name
From 'Table'
Where mon > Month(CurDate())
   or ((mon = Month(CurDate())) 
       and (day >= DayOfMonth(CurDate())))
union
Select 2,mon,day,name
From 'Table'
Where mon < Month(CurDate())
   or ((mon = Month(CurDate())) 
       and (day < DayOfMonth(CurDate())))
Order By 1,2,3