getting the ranks of items

I'm probably asking a FAQ, but a few google searches didn't seem
to point me in the right place.

Is there a simple way with PostgreSQL to assign relative ranks to the
result of a query ORDER BY? That is, I want to either have a view
that cheaply assigns the ranks, or be able to update a column with the
current ranks (yes, I know this latter version is more prone to
error).

I'm certain there's probably something I can do to laminate an array
value to a query result. Am I confused? (Yes!)

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn(at)stonehenge(dot)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!

On May 3, 2005, at 8:30 PM, Randal L. Schwartz wrote:

> Is there a simple way with PostgreSQL to assign relative ranks to the
> result of a query ORDER BY?

What do you mean by ranks?

>>>>> "Matthew" == Matthew Terenzio <matt(at)jobsforge(dot)com> writes:

Matthew> On May 3, 2005, at 8:30 PM, Randal L. Schwartz wrote:

>> Is there a simple way with PostgreSQL to assign relative ranks to the
>> result of a query ORDER BY?

Matthew> What do you mean by ranks?

If I order a query by ascending age, the youngest person gets
rank 1, the second youngest gets rank 2, the third youngest gets rank 3,
and if the fourth and fifth tie, they both get 4, and the next one gets 6.

You know, rank? :)

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn(at)stonehenge(dot)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!

Randal L. Schwartz wrote:

>I'm probably asking a FAQ, but a few google searches didn't seem
>to point me in the right place.
>
>Is there a simple way with PostgreSQL to assign relative ranks to the
>result of a query ORDER BY? That is, I want to either have a view
>that cheaply assigns the ranks, or be able to update a column with the
>current ranks (yes, I know this latter version is more prone to
>error).
>
>I'm certain there's probably something I can do to laminate an array
>value to a query result. Am I confused? (Yes!)
>
>
>

Randal,

May be you can use something like this:

create sequence seq_tmp;
select nextval('seq_tmp') as rank, a.id, a.name from (select id, name
from t order by name desc) a;
drop sequence seq_tmp;

I don't know how cheap will this be (because of the sequence), but
couldn't find another way. I do not think that we have something like
Oracle's ROWNUM...

Regards,
Lyubomir Petrov

P.S. I'm sure you can wrap it in plperl stored procedure :)

How about something like:

CREATE TABLE testrank (
id int,
value varchar
);

insert into testrank values(17,'way');
insert into testrank values(27,'foo');
insert into testrank values(278,'bar');
insert into testrank values(1,'abd');
insert into testrank values(2,'def');

CREATE OR REPLACE FUNCTION ranker(text) RETURNS SETOF RECORD AS $$
my ($query) = @_;

my $rv = spi_exec_query($query);
my $rows = [];
foreach my $rn (0 .. ($rv->{processed})) {
my $row = $rv->{rows}[$rn];
$row->{index} = $rn+1;
push @$rows,$row;
}
return $rows;
$$ language plperl;

select * from ranker('select * from testrank order by value') as t(index
int,id int,value varchar);

1,1,"abc"
2,278,"bar"
3,2,"def"
4,27,"foo"
5,17,"way"

Sorry, the results don't paste in very well, but you get the idea. This
would probably need to be cleaned up a bit, but I think would do something
like what you need.

Sean

----- Original Message -----
From: "Lyubomir Petrov" <lpetrov(at)sysmaster(dot)com>
To: "Randal L. Schwartz" <merlyn(at)stonehenge(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, May 03, 2005 9:13 PM
Subject: Re: [GENERAL] getting the ranks of items

> Randal L. Schwartz wrote:
>
>>I'm probably asking a FAQ, but a few google searches didn't seem
>>to point me in the right place.
>>
>>Is there a simple way with PostgreSQL to assign relative ranks to the
>>result of a query ORDER BY? That is, I want to either have a view
>>that cheaply assigns the ranks, or be able to update a column with the
>>current ranks (yes, I know this latter version is more prone to
>>error).
>>
>>I'm certain there's probably something I can do to laminate an array
>>value to a query result. Am I confused? (Yes!)
>>
>>
>
> Randal,
>
> May be you can use something like this:
>
>
> create sequence seq_tmp;
> select nextval('seq_tmp') as rank, a.id, a.name from (select id, name from
> t order by name desc) a;
> drop sequence seq_tmp;
>
>
> I don't know how cheap will this be (because of the sequence), but
> couldn't find another way. I do not think that we have something like
> Oracle's ROWNUM...
>
>
> Regards,
> Lyubomir Petrov
>
> P.S. I'm sure you can wrap it in plperl stored procedure :)
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

>
> If I order a query by ascending age, the youngest person gets
> rank 1, the second youngest gets rank 2, the third youngest gets rank 3,
> and if the fourth and fifth tie, they both get 4, and the next one gets 6.
>
> You know, rank? :)

You could use a plPerl function.

Sincerely,

Joshua D. Drake

> > If I order a query by ascending age, the youngest person gets
> > rank 1, the second youngest gets rank 2, the third youngest gets rank 3,
> > and if the fourth and fifth tie, they both get 4, and the next one gets 6.
> >
> > You know, rank? :)
>
> You could use a plPerl function.

To do it with ties, you'd need some way of passing the function the ranking
criteria with persistence between calls, which might have some startup issues.

Wouldn't that also cause problems with multiple users calling the function
simultaneously?
--
Mike Nolan

In article <427821D7(dot)7070302(at)sysmaster(dot)com>,
Lyubomir Petrov <lpetrov(at)sysmaster(dot)com> writes:

> create sequence seq_tmp;
> select nextval('seq_tmp') as rank, a.id, a.name from (select id, name
> from t order by name desc) a;
> drop sequence seq_tmp;

Using a temporary sequence for that would avoid naming conflicts.

> P.S. I'm sure you can wrap it in plperl stored procedure :)

Yes, prepending the ranking column in the application would be more efficient.
I wonder whether Randall knows Perl? ;-)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well, if you don't need the ranks to be sequential, merely ordered:

CREATE TABLE ranker (id INT, age INT);
...
SELECT b.w-a.age AS rank, a.id, a.age
FROM (SELECT * FROM ranker ORDER BY age DESC, id) AS a,
(SELECT max(age)+1 AS w FROM ranker) as b;

rank | id | age
- ------+----+-----
1 | 5 | 22
3 | 2 | 20
3 | 3 | 20
3 | 8 | 20
7 | 4 | 16
7 | 7 | 16
11 | 6 | 12
13 | 1 | 10

However, if you *do* need them to be sequential:

SELECT setval('rank1', 1);
SELECT setval('rank2', 1);
SELECT setval('rank_seq', 1, false);

SELECT CASE WHEN a.age = b.age THEN currval('rank_seq') ELSE nextval('rank_seq') END AS rank,
a.id, a.age
FROM
(
SELECT nextval('rank1') AS ct, a.* FROM (SELECT id,age FROM ranker ORDER BY age DESC, id) AS a
UNION ALL
SELECT nextval('rank1') AS ct,null,null
) AS a
,
(
SELECT nextval('rank2') AS ct,null AS id,null AS age
UNION ALL
SELECT nextval('rank2') AS ct, b.* FROM (SELECT id,age FROM ranker ORDER BY age DESC, id) AS b
) AS b
WHERE a.ct = b.ct AND a.age IS NOT NULL
;

rank | id | age
- ------+----+-----
1 | 5 | 22
2 | 2 | 20
2 | 3 | 20
2 | 8 | 20
3 | 4 | 16
3 | 7 | 16
4 | 6 | 12
5 | 1 | 10

Neither of which are terribly efficient, but that wasn't a prerequisite :)

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200505022047
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFCdssgvJuQZxSWSsgRAnbMAKCZyehHPTarYGB7YqkYFOrafOF1KwCg8V7E
3fveOsUWj2AgWtmQdR7S/uU=
=KcOL
-----END PGP SIGNATURE-----

>>>>> "Harald" == Harald Fuchs <use_reply_to(at)protecting(dot)net> writes:

Harald> Using a temporary sequence for that would avoid naming conflicts.

>> P.S. I'm sure you can wrap it in plperl stored procedure :)

Well, yes. I was (falsely?) recalling that there was a pure SQL way
to do this though.

And the point of doing it as part of the (sub)query is that I was then
going to do a further join and select on this.

Harald> Yes, prepending the ranking column in the application would be
Harald> more efficient. I wonder whether Randall knows Perl? ;-)

If not, I'm sure I could dig up a couple of books to learn it. :)

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn(at)stonehenge(dot)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!

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