On May 4, 2005, at 20:50, Randal L. Schwartz wrote:
Well, yes. I was (falsely?) recalling that there was a pure SQL way to do this though.
Here's a pure SQL method. There might be more performant ways of rewriting the query, but this should do what you want.
test=# create table persons (
person_name text not null unique
, birthdate date not null
) without oids;
NOTICE: CREATE TABLE / UNIQUE will create implicit index "persons_person_name_key" for table "persons"
CREATE TABLE
test=# copy persons (person_name, birthdate) from stdin;
Emily 1999-01-01
Sarah 1998-01-01
Brianna 1999-01-01
Jacob 2001-01-02
Michael 1993-01-01
Matthew 2005-01-01
\.
>> >> >> >> >> >> test=#
test=# select person_name, age(birthdate)
from persons
order by age asc;
person_name | age
-------------+------------------------
Matthew | 4 mons 3 days
Jacob | 4 years 4 mons 2 days
Emily | 6 years 4 mons 3 days
Brianna | 6 years 4 mons 3 days
Sarah | 7 years 4 mons 3 days
Michael | 12 years 4 mons 3 days
(6 rows)
test=# select p1.person_name
, (select count(*)
from (
select *
from persons p2
having age(p2.birthdate) > age(p1.birthdate)
) as foo
) + 1 as rank
from persons p1
order by rank asc;
person_name | rank
-------------+------
Michael | 1
Sarah | 2
Emily | 3
Brianna | 3
Jacob | 5
Matthew | 6
(6 rows)This utilizes what I've heard called a "correlated subquery", as the subquery in the select list is run for each row of the result (note the p1 and p2 in the HAVING clause). I believe this correlated subquery can also be written using a join, but would have to do further digging to find the code.
The + 1 gives ranks starting at 1 rather than 0.
I believe Joe Celko's "SQL for Smarties" includes more varieties of this as well. I wouldn't be surprised if that's also where I originally got the code :)
Hope this helps!
Michael Glaesemann grzm myrealbox com
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
