How should I handle "ranked x out of y" data in PostgreSQL?

Question

I have a table that I would like to be able to present "ranked X out of Y" data for. In particular, I'd like to be able to present that data for an individual row in a relatively efficient way (i.e. without selecting every row in the table). The ranking itself is quite simple, it's a straight ORDER BY on a single column in the table.

Postgres seems to present some unique challenges in this regard; AFAICT it doesn't have a RANK or ROW_NUMBER or equivalent function (at least in 8.3, which I'm stuck on for the moment). The canonical answer in the mailing list archives seems to be to create a temporary sequence and select from it:

test=> create temporary sequence tmp_seq;
CREATE SEQUENCE
test=*> select nextval('tmp_seq') as row_number, col1, col2 from foo;

It seems like this solution still won't help when I want to select just a single row from the table (and I want to select it by PK, not by rank).

I could denormalize and store the rank in a separate column, which makes presenting the data trivial, but just relocates my problem. UPDATE doesn't support ORDER BY, so I'm not sure how I'd construct an UPDATE query to set the ranks (short of selecting every row and running a separate UPDATE for each row, which seems like way too much DB activity to trigger every time the ranks need updating).

Am I missing something obvious? What's the Right Way to do this?

EDIT: Apparently I wasn't clear enough. I'm aware of OFFSET/LIMIT, but I don't see how it helps solve this problem. I'm not trying to select the Xth-ranked item, I'm trying to select an arbitrary item (by its PK, say), and then be able to display to the user something like "ranked 43rd out of 312."

Answer 1

If you want the rank, do something like

SELECT id,num,rank FROM (
  SELECT id,num,rank() OVER (ORDER BY num) FROM foo
) AS bar WHERE id=4

Or if you actually want the row number, use

SELECT id,num,row_number FROM (
  SELECT id,num,row_number() OVER (ORDER BY num) FROM foo
) AS bar WHERE id=4

They'll differ when you have equal values somewhere. There is also dense_rank() if you need that.

This requires PostgreSQL 8.4, of course.

Answer 2

Isn't it just this:

SELECT  *
FROM    mytable
ORDER BY
        col1
OFFSET X LIMIT 1

Or I am missing something?

Update:

If you want to show the rank, use this:

SELECT  mi.*, values[1] AS rank, values[2] AS total
FROM    (
        SELECT  (
                SELECT  ARRAY[SUM(((mi.col1, mi.ctid) < (mo.col1, mo.ctid))::INTEGER), COUNT(*)]
                FROM    mytable mi
                ) AS values
        FROM    mytable mo
        WHERE   mo.id = @myid
        ) q