Re: getting the ranks of items

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