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