Message-ID: <3D12E43E.3090307@shifted-bytes.de>
Date: Fri, 21 Jun 2002 10:30:54 +0200
From: Michael Agbaglo <byteshifter@shifted-bytes.de>
User-Agent: Mozilla/5.0 (Windows; U; Win98; en-US;
	rv:0.9.4.1) Gecko/20020508 Netscape6/6.2.3
MIME-Version: 1.0
To: josh@agliodbs.com
Cc: pgsql-sql@postgresql.org
Subject: Re: how to sort a birthday list ?
References: <3D122508.70104@shifted-bytes.de>
	<200206201703.52435.josh@agliodbs.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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.
> 
> 
> 
> 
> 
>