MYSQL - Upcoming X birth dates

Question

I have a table containing names and birth dates.
I would like to get the names corresponding to the n next upcoming birthdates. (where n is an integer)
This is not possible by using LIMIT (I don't want to limit the number of rows), neither by using BETWEEN or WHERE clause because it is not in a limited time.
Thank's for your help

Answer 1

The following query ...

1) first sorts distinct dob's (by default, today is sorted first).

2) selects the first dob's using the LIMIT function, and

3) lastly, selects all the users having the earliest 5 dob's.

SELECT u1.*
from USERS u1
INNER JOIN
(  
    SELECT DISTINCT 
      EXTRACT(MONTH FROM dob) dob_month,
      EXTRACT(DAY FROM dob) dob_day
    FROM users
    ORDER BY 
    CASE
      WHEN EXTRACT(MONTH FROM dob) = EXTRACT(MONTH FROM CURDATE()) AND EXTRACT(DAY FROM dob) = EXTRACT(DAY FROM CURDATE()) THEN 1
      WHEN EXTRACT(MONTH FROM dob) = EXTRACT(MONTH FROM CURDATE()) AND EXTRACT(DAY FROM dob) < EXTRACT(DAY FROM CURDATE()) THEN -1
      ELSE SIGN(EXTRACT(MONTH FROM dob) - EXTRACT(MONTH FROM CURDATE()))
    END DESC, 
    CASE
      WHEN EXTRACT(MONTH FROM dob) = EXTRACT(MONTH FROM CURDATE()) AND EXTRACT(DAY FROM dob) = EXTRACT(DAY FROM CURDATE()) THEN 1
      ELSE SIGN(EXTRACT(DAY FROM dob) - EXTRACT(DAY FROM CURDATE()))
    END DESC,     
    EXTRACT(MONTH FROM dob) ASC,
    EXTRACT(DAY FROM dob) ASC
  LIMIT 5
) selected_dobs
ON EXTRACT(MONTH FROM u1.dob) = selected_dobs.dob_month and EXTRACT(DAY FROM u1.dob) = selected_dobs.dob_day
ORDER BY
    CASE
      WHEN EXTRACT(MONTH FROM dob) = EXTRACT(MONTH FROM CURDATE()) AND EXTRACT(DAY FROM dob) = EXTRACT(DAY FROM CURDATE()) THEN 1
      WHEN EXTRACT(MONTH FROM dob) = EXTRACT(MONTH FROM CURDATE()) AND EXTRACT(DAY FROM dob) < EXTRACT(DAY FROM CURDATE()) THEN -1
      ELSE SIGN(EXTRACT(MONTH FROM dob) - EXTRACT(MONTH FROM CURDATE()))
    END DESC, 
    CASE
      WHEN EXTRACT(MONTH FROM dob) = EXTRACT(MONTH FROM CURDATE()) AND EXTRACT(DAY FROM dob) = EXTRACT(DAY FROM CURDATE()) THEN 1
      ELSE SIGN(EXTRACT(DAY FROM dob) - EXTRACT(DAY FROM CURDATE()))
    END DESC,     
    EXTRACT(MONTH FROM dob) ASC,
    EXTRACT(DAY FROM dob) ASC, 
    username ASC;

SQL Fiddle demo

NOTE

No rows are filtered out from the SELECT, because it is possible that (some or all of) the next 5 birthdays occur in the next calendar year (for e.g. today could be Dec 28 and the next 5 birthdays might occur on Dec 29, Dec 31, Jan 2, Jan 5, Jan 6).

If you would like today's birthdays to be selected at the bottom of the list (instead of the top of the list, as done in the above query), just change 1 to -1 in the ORDER BY clause, as below

ORDER BY CASE WHEN EXTRACT(MONTH FROM dob) = EXTRACT(MONTH FROM CURDATE()) AND EXTRACT(DAY FROM dob) = EXTRACT(DAY FROM CURDATE()) THEN -1 WHEN EXTRACT(MONTH FROM dob) = EXTRACT(MONTH FROM CURDATE()) AND EXTRACT(DAY FROM dob) < EXTRACT(DAY FROM CURDATE()) THEN -1 ELSE SIGN(EXTRACT(MONTH FROM dob) - EXTRACT(MONTH FROM CURDATE())) END DESC, CASE WHEN EXTRACT(MONTH FROM dob) = EXTRACT(MONTH FROM CURDATE()) AND EXTRACT(DAY FROM dob) = EXTRACT(DAY FROM CURDATE()) THEN -1 ELSE SIGN(EXTRACT(DAY FROM dob) - EXTRACT(DAY FROM CURDATE())) END DESC, EXTRACT(MONTH FROM dob) ASC, EXTRACT(DAY FROM dob) ASC

Answer 2

First off, you're going to want a Calendar table (this particular example is for SQL Server, but should be adaptable). It's one of the most useful analysis/dimension tables, and you can often use them to get index-based access for queries that would normally be non SARG-able.

For this case we need a minimum table like this:

CREATE TABLE (isoDate DATE,
              dayOfMonth INTEGER,
              month INTEGER)

...and two indices, starting with [isoDate, dayOfMonth, month] and [dayOfMonth, month, isoDate] (or [month, dayOfMonth, isoDate]).

We can now use this to get the next 5 birthdays:

SELECT DISTINCT Next.isoDate, Next.month, Next.dayOfMonth
FROM Users
JOIN Calendar Start
  ON Start.isoDate = Users.dateOfBirth
JOIN Calendar Next
  ON Next.isoDate >= CURDATE()
     AND Next.month = Start.month
     AND Next.dayOfMonth = Start.dayOfMonth
ORDER BY Next.isoDate 
LIMIT 5

This will get all next instances of month/day-of-month combinations, looping years (and including people multiple times if necessary). Note that this doesn't move people with birthdays on February 29th (so those people won't have a birthday every year).

We can now join back again to get names:

SELECT Birthdays.isoDate AS dateOfBirth, Users.name
FROM (SELECT DISTINCT Next.isoDate, Next.month, Next.dayOfMonth
      FROM Users
      JOIN Calendar Start
        ON Start.isoDate = Users.dateOfBirth
      JOIN Calendar Next
        ON Next.isoDate >= CURDATE()
           AND Next.month = Start.month
           AND Next.dayOfMonth = Start.dayOfMonth
      ORDER BY Next.isoDate 
      LIMIT 5) Birthdays
JOIN Calendar Origin
  ON Origin.month = Birthdays.month
     AND Origin.dayOfMonth = Birthdays.dayOfMonth
JOIN Users 
  ON Users.dateOfBirth = Origin.isoDate
ORDER BY Birthdays.isoDate

And you're golden