[RESOLVED] Sort by next birthday

IAM2Phantazm

I'm trying to write a query that will order by the next upcoming birthday.... for example... if John's b-day is 1980-01-25 and Jane's is 1990-05-14 and today is 2007-08-12 -- I wish the order to be John,Jane -- as John's bday comes up next.

Here's the (admittedly terrible) query I'm using --- I'g guessing it is close 😛

SELECT UserID,Username,Birthday FROM `table` ORDER BY DATEDIFF(NOW('MD'),DATE_FORMAT(Birthday,'%m%d'))

Birthday is a simple date field (yyyy-mm-dd)

rulian

Well, if you just want to sort by birtday order in the calendar, the year they were born is irrelevant,
This my not be the easiest or most practical way of doing it, but I'd timestamp (date format their birthdays to day-month and keep todays year), and have a timestamp(today) comparison query and subtract them.
Past birthdays should come up negative numbers, the less the number the further away, and positive numbers should have the same result, where as the greater the number the further away the birthday.

IAM2Phantazm

Well, that's gotten me closer than anyone else has so far! 🙂 Thanks! Almost got this now.. but with a little variation on your idea.

As opposed to comparing the dates themselves (DATEDIFF always seems to give a NULL when I try to "drop" the year from the comparison) here's what I've come up with...

SELECT Username,Birthday, DATE_FORMAT(NOW(),'%c%d') - DATE_FORMAT(Birthday,'%c%d') AS BDay FROM `table` WHERE ORDER BY DATE_FORMAT(NOW(),'%c%d') - DATE_FORMAT(Birthday,'%c%d') ASC

(And the BDay is just in there so I can visually see the result of the computation)

Of course I get negative values for all birthdays that haven't yet occured this year (which is great) and positive values for any that have occured. However, the order is something like this:

User1     1980-08-31     -18
User2     1975-08-24     -11
User3     1978-08-13     0
User4     1985-07-23     90
User5     1973-06-12     201

The desired order would be User3,User2,User1,User5,User4.

That said, that query looks (to me anyway) terribly written, and that is the fruit of a good hour's labor! If anyone happens to just know how to do this simply posting a query would be cool.. 😛 (But simple help is certainly approciated, too!)

Roger_Ramjet

Well NEXT_OCCURENCE(start, interval) is a function that I think should be included as part of all rdbms, but never is. It is something that crops up whenever you are dealing with events that occur periodically: next birthday, next meeting, next match, etc. So I have coded my own user defined function for it several times in other dbs. Not such an easy proposition in mysql.

So the best bet is to use an IF() in the select:

SELECT  IF(Bday < 0, Bday+366, Bday) as Bnum ... ORDER BY Bnum

I'll leave you to do the rest: you may need to use a subquery.

Personally I would create a VIEW that calculated the next birthday as an actual date for each user but you may not feel the need.

IAM2Phantazm

I think I prefer the VIEW scenario here...I'll play with the info you provided and post again in a bit. Thank you! (And ya... NEXT_OCCURENCE would be really nice! 😛)

IAM2Phantazm

I don't know that this is the best solution, but considering I've been fighting this dam thing for days now, here's what I've used:

$sql = "
(SELECT Field1, Field2, DAYOFYEAR(Birthday) AS SortBy FROM `table` WHERE DAYOFYEAR(Birthday) >= DAYOFYEAR(NOW()))

UNION

(SELECT Field1, Field2, DAYOFYEAR(Birthday) + 366 AS SortBy FROM `table` WHERE DAYOFYEAR(Birthday) < DAYOFYEAR(NOW()))

ORDER BY SortBy LIMIT ".$begin.",".$perpage;

I suppose that isn't the most elegent solution out there, but it DOES do what I wish and is less computationally expensive than many of the alternatives I've explored.

Roger_Ramjet

Well if it is the most computationally efficient then it is the most elegant as well. Cool if all you want is the sort order without the actual next birthday itself.

PS Personally I like UNION queries myself just because they make things like this simpler.

IAM2Phantazm

Yes, all I need is a way to SORT in this order... and it basically came down to either a UNION or a rather computationally expensive query with loads of mysql function calls (i.e. DATEADD, DATEDIFF, etc..).

Perhaps this isn't the best way to do this, but at the moment it'll do.. 🙂 (This is designed to simply sort a list of users by next b-day... how far off the day in question may be is unimportant here, so long as they're sorted)

However, it has been brought to my attention that using UNION can be rather MEMORY intensive.... I'm not sure how practical this query would be if we're talking about, say, 100k records...? Comments on that?

Roger_Ramjet

The only way to tell which method is most 'expensive' is to benchmark them.

Unions of simple queries on the same tables with different conditions are less efficient because they involve multiple passes across the table, and in your case a further pass across the results for the outer sort. Chances are that Explain would show the use of a temp table and filesort. So I would guess that it is not memory but disk IO that is the issue.