From: Michael Agbaglo <byteshifter@shifted-bytes.de>
To: josh@agliodbs.com
Cc: pgsql-sql@postgresql.org
Subject: Re: how to sort a birthday list ?
Date: Fri, 21 Jun 2002 10:30:54 +0200
Message-ID: <3D12E43E.3090307@shifted-bytes.de> (raw)
References: <3D122508.70104@shifted-bytes.de>
<200206201703.52435.josh@agliodbs.com>
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.
>
>
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: byteshifter@shifted-bytes.de, josh@agliodbs.com
Subject: Re: how to sort a birthday list ?
In-Reply-To: <3D12E43E.3090307@shifted-bytes.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox