mysql - Sort upcoming birthdays based on current date

Question

I have the following table of people and their birthdays:

name        birthday
----------------------
yannis      1979-06-29
natalia     1980-08-19
kostas      1983-10-27    
christos    1979-07-22
kosmas      1978-04-28

and I have no idea how to sort the names on how closer the birthday is to today. So for NOW() = 2011-09-08 the sorted result should be:

kostas      1983-10-27
kosmas      1978-04-28
yannis      1979-06-29
christos    1979-07-22
natalia     1980-08-19

I'm looking for a quick hack, don't really care for performance (pet project - table will hold less than 1000 records), but of course every suggestion will be extremely appreciated.

Answer 1

Here is one way:

• Calculate current year - year of birth
• Add the resulting number of years to the date of birth
• You now have the birthday this year, if this date has passed then add one more year
• Sort the results by that date

SELECT
    name,
    birthday,
    birthday + INTERVAL (YEAR(CURRENT_DATE) - YEAR(birthday))     YEAR AS currbirthday,
    birthday + INTERVAL (YEAR(CURRENT_DATE) - YEAR(birthday)) + 1 YEAR AS nextbirthday
FROM birthdays
ORDER BY CASE
    WHEN currbirthday >= CURRENT_DATE THEN currbirthday
    ELSE nextbirthday
END

Notes:

• Today's birthdays appears first regardless of current time
• February 29 birthday is treated equal to February 28 birthday for common years e.g.
  • On Jan/1/2019 both Feb 28 and Feb 29 birthdays (2019) are sorted equal
  • On Mar/1/2019 Feb 28 and Feb 29 birthdays (2020) are sorted as expected

SQLFiddle