FAQ
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@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!

Search Discussions

  • Matthew Terenzio at May 4, 2005 at 12:55 am

    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?
  • Randal L. Schwartz at May 4, 2005 at 12:57 am
    "Matthew" == Matthew Terenzio 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@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!
  • Joshua D. Drake at May 4, 2005 at 1:55 am

    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
  • Mike Nolan at May 4, 2005 at 2:12 am

    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
  • Lyubomir Petrov at May 4, 2005 at 1:14 am

    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 :)
  • Sean Davis at May 4, 2005 at 1:46 am
    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@sysmaster.com>
    To: "Randal L. Schwartz" <merlyn@stonehenge.com>
    Cc: <pgsql-general@postgresql.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@postgresql.org)
  • Harald Fuchs at May 4, 2005 at 11:23 am
    In article <427821D7.7070302@sysmaster.com>,
    Lyubomir Petrov <lpetrov@sysmaster.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? ;-)
  • Randal L. Schwartz at May 4, 2005 at 11:51 am
    "Harald" == Harald Fuchs 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@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!
  • Michael Glaesemann at May 4, 2005 at 1:45 pm

    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
  • Greg Sabino Mullane at May 4, 2005 at 11:29 am
    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@turnstep.com
    PGP Key: 0x14964AC8 200505022047
    http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedMay 4, '05 at 12:30a
activeMay 4, '05 at 1:45p
posts11
users9
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase