>>>>> "Svenne" == Svenne Krap <[EMAIL PROTECTED]> writes:
Svenne> I kind of found the answer myself ... here are some snipplets ... Svenne> the table and the data : Svenne> CREATE SEQUENCE "friends_friendid_seq" start 1 increment 1 maxvalue Svenne> 2147483647 minvalue 1 cache 1 ; Svenne> CREATE TABLE "friends" ( Svenne> "friendid" integer DEFAULT Svenne> nextval('"friends_friendid_seq"'::text) NOT NULL, Svenne> "friendname" character varying, Svenne> "dateofbirth" timestamp with time zone, Svenne> Constraint "friends_pkey" Primary Key ("friendid") Svenne> ); Svenne> COPY "friends" FROM stdin; Svenne> 1 Tony 1978-01-28 00:00:00+01 Svenne> 2 Gary 1966-06-04 00:00:00+01 Svenne> 3 Jodie 1979-01-11 00:00:00+01 Svenne> \. Svenne> My query, works but looks clumbersome.. can it be made smarter ? Svenne> select * from ( Svenne> (select *,date_part('year',now()) - date_part('year', dateofbirth) as Svenne> age, date_part('doy',dateofbirth)-date_part('doy',now()) as daystogo Svenne> from friends where date_part('doy',dateofbirth) >= Svenne> date_part('doy',now())) Svenne> union Svenne> (select *,date_part('year',now()) - date_part('year', dateofbirth ) +1 Svenne> as age, date_part('doy',dateofbirth)-date_part('doy',now()) + Svenne> date_part('day', (now() + '1 year'::interval)::timestamp - now()) as Svenne> daystogo from friends where date_part('doy',dateofbirth) < Svenne> date_part('doy',now()))) Svenne> r order by r.daystogo yeah, how about a little modular arithmetic? select friendname, dateofbirth from friends order by (366 + date_part('doy', dateofbirth) - date_part('doy', now())) % 366; -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 <[EMAIL PROTECTED]> <URL:http://www.stonehenge.com/merlyn/> Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training! ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]