Обсуждение: Birthsday list

Hi.

I have a table roughly like this

create table friends(
friendid serial,
friendname varchar,
dateofbirth timestamp,
primary id(friendid));

and the data looks like


1    Tony    1978/01/28
2    Gary    1966/06/04
3    Jodie    1979/01/11

and so on..

How do i select from the table, so that I get the tuples ordered with
the one having birthsday first from now first (and then ascending
order) ???

Tia

Svenne
--
Mail usenet@krap.dk - svenne@krap.dk - PGP key id : 0xDF484022
ICQ: 5434480 - http://www.krap.dk - http://www.krap.net
PGP Key http://keys.pgp.dk:11371/pks/lookup?op=get&search=0xDF484022

I kind of found the answer myself ... here are some snipplets ...

the table and the data :


CREATE SEQUENCE "friends_friendid_seq" start 1 increment 1 maxvalue
2147483647 minvalue 1  cache 1 ;

CREATE TABLE "friends" (
        "friendid" integer DEFAULT
nextval('"friends_friendid_seq"'::text) NOT NULL,
        "friendname" character varying,
        "dateofbirth" timestamp with time zone,
        Constraint "friends_pkey" Primary Key ("friendid")
);

COPY "friends"  FROM stdin;
1       Tony    1978-01-28 00:00:00+01
2       Gary    1966-06-04 00:00:00+01
3       Jodie   1979-01-11 00:00:00+01
\.


My query, works but looks clumbersome.. can it be made smarter ?

select * from (
(select *,date_part('year',now()) - date_part('year', dateofbirth) as
age,  date_part('doy',dateofbirth)-date_part('doy',now()) as daystogo
from friends where date_part('doy',dateofbirth) >=
date_part('doy',now()))
union
(select *,date_part('year',now()) - date_part('year', dateofbirth ) +1
as age, date_part('doy',dateofbirth)-date_part('doy',now()) +
date_part('day', (now() + '1 year'::interval)::timestamp -  now()) as
daystogo from friends where date_part('doy',dateofbirth) <
date_part('doy',now())))
 r order by r.daystogo

Tia

Svenne
--
Mail usenet@krap.dk - svenne@krap.dk - PGP key id : 0xDF484022
ICQ: 5434480 - http://www.krap.dk - http://www.krap.net
PGP Key http://keys.pgp.dk:11371/pks/lookup?op=get&search=0xDF484022

>>>>> "Svenne" == Svenne Krap <usenet@krap.dk> 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
<merlyn@stonehenge.com> <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!