Thread: how to sort a birthday list ?
Hi ! there's a nice query for retrieving the people who have their birthday in the next n days: (taken from Joe Celko's SQL for Smarties, 1st Ed., p. 76) SELECT * FROM Persons WHERE EXTRACT( YEAR FROM AGE(dateofbirth) ) < EXTRACT( YEAR FROM AGE( CURRENT_DATE+60, dateofbirth ) ) ... but how do I sort the list ?
On Thu, 2002-06-20 at 13:55, Michael Agbaglo wrote: > Hi ! > > there's a nice query for retrieving the people who have their birthday > in the next n days: (taken from Joe Celko's SQL for Smarties, 1st Ed., > p. 76) > > SELECT * > FROM Persons > WHERE EXTRACT( YEAR FROM AGE(dateofbirth) ) < EXTRACT( YEAR FROM AGE( > CURRENT_DATE+60, dateofbirth ) ) > > ... but how do I sort the list ? How about: ORDER BY dateofbirth
Michael, > SELECT * > FROM Persons > WHERE EXTRACT( YEAR FROM AGE(dateofbirth) ) < EXTRACT( YEAR FROM AGE( > CURRENT_DATE+60, dateofbirth ) ) > > ... but how do I sort the list ? Easy: SELECT person_name, person_department, EXTRACT( YEAR FROM AGE(dateofbirth) ) as their_age FROM Persons WHERE EXTRACT( YEAR FROM AGE(dateofbirth) ) < EXTRACT( YEAR FROM AGE( CURRENT_DATE+60, dateofbirth ) ) ORDER BY their_age, person_name As an example. -- -Josh Berkus
Dear Josh, if you sort by AGE then the order will be young -> old or vice versa. I'd like to have the list sorted as interval birthDAY, birthMONTH and DAY from NOW() and MONTH from NOW(). example: 22.06.64 Person-1 26.06.50 Person-2 01.08.69 Person-3 02.08.71 Person-4 ... of course you could sort by DOY but then you'll have a problem w/ the next year: if it's let's say december and you select the list for the next 60 days, persons having birthday in december will appear after persons having birthday in january. I tried to use CASE WHEN... THEN in ORDER BY but it doesn't seem to work (syntax error at '') M. Josh Berkus wrote: > Michael, > > >>SELECT * >>FROM Persons >>WHERE EXTRACT( YEAR FROM AGE(dateofbirth) ) < EXTRACT( YEAR FROM AGE( >>CURRENT_DATE+60, dateofbirth ) ) >> >>... but how do I sort the list ? >> > > Easy: > > SELECT person_name, person_department, EXTRACT( YEAR FROM AGE(dateofbirth) ) > as their_age > FROM Persons > WHERE EXTRACT( YEAR FROM AGE(dateofbirth) ) < EXTRACT( YEAR FROM AGE( > CURRENT_DATE+60, dateofbirth ) ) > ORDER BY their_age, person_name > > As an example. > > > > > >
David Stanaway wrote: > > How about: > ORDER BY dateofbirth > > > doesn't work: it's sorted by YEAR ...
On Fri, Jun 21, 2002 at 10:30:54 +0200, Michael Agbaglo <byteshifter@shifted-bytes.de> wrote: > > of course you could sort by DOY but then you'll have a problem w/ the > next year: > > if it's let's say december and you select the list for the next 60 days, > persons having birthday in december will appear after persons having > birthday in january. > > I tried to use CASE WHEN... THEN in ORDER BY but it doesn't seem to work > (syntax error at '') You also need to worry about leap years. If a birthday is February 29 and there isn't one this year, what do you want to happen?
Bruno Wolff III wrote: > > On Fri, Jun 21, 2002 at 10:30:54 +0200, > Michael Agbaglo <byteshifter@shifted-bytes.de> wrote: > > > > of course you could sort by DOY but then you'll have a problem w/ the > > next year: > > > > if it's let's say december and you select the list for the next 60 days, > > persons having birthday in december will appear after persons having > > birthday in january. > > > > I tried to use CASE WHEN... THEN in ORDER BY but it doesn't seem to work > > (syntax error at '') > > You also need to worry about leap years. If a birthday is February 29 > and there isn't one this year, what do you want to happen? You can create a little PL/pgSQL function like this: CREATE FUNCTION next_birthday(date) RETURNS date AS ' DECLARE p_dob ALIAS FOR $1; v_age integer; v_birthday date; BEGIN -- First we get the age in years v_age := EXTRACT (YEAR FROM CURRENT_DATE) - EXTRACT (YEAR FROM p_dob); -- We add that to the DOB to get this years birthday v_birthday := p_dob + (v_age::text || '' years'')::interval; -- If that is in the past, we add another year IF v_birthday < CURRENT_DATE THEN v_birthday := v_birthday + ''1year''::interval; END IF; RETURN v_birthday; END;' LANGUAGE plpgsql; It just calculates the next birthday of a person relative from today. Then query with SELECT next_birthday(birthday), name FROM person ORDER BY 1; Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #