0

The issue with 8.3 is that rank() is introduced in 8.4.

Consider the numbers [10,6,6,2].
I wish to achieve a rank of those numbers where the rank is equal to the row number:

rank | score
-----+------
1    | 10
2    | 6
3    | 6
4    | 2

A partial solution is to self-join and count items with a higher or equal, score. This produces:

1    | 10
3    | 6
3    | 6
4    | 2

But that's not what I want.
Is there a way to rank, or even just order by score somehow and then extract that row number?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Arthur
  • 3,376
  • 11
  • 43
  • 70
  • Why not upgrade to a current version? Postgres 8.3 is end of life: http://www.postgresql.org/support/versioning/ –  Jan 25 '13 at 13:31
  • because, you cant just press a button to upgrade. I am in the process of migrating to 9.1 but i need this to work in 8.3 – Arthur Jan 25 '13 at 13:33
  • Your question is ambiguous. Do you want the equivalent of [`rank()` or `row_number()`](http://www.postgresql.org/docs/current/interactive/functions-window.html)? – Erwin Brandstetter Jan 25 '13 at 16:50

3 Answers3

2

If you want a row number equivalent to the window function row_number(), you can improvise in version 8.3 (or any version) with a (temporary) SEQUENCE:

CREATE TEMP SEQUENCE foo;
    
SELECT nextval('foo') AS rn, *
FROM   (SELECT score FROM tbl ORDER BY score DESC) s;

db<>fiddle here
Old sqlfiddle

The subquery is necessary to order rows before calling nextval().

Note that the sequence (like any temporary object) ...

  • is only visible in the same session it was created.
  • hides any other table object of the same name.
  • is dropped automatically at the end of the session.

To use the sequence in the same session repeatedly run before each query:

SELECT setval('foo', 1, FALSE);
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

There's a method using an array that works with PG 8.3. It's probably not very efficient, performance-wise, but will do OK if there aren't a lot of values.

The idea is to sort the values in a temporary array, then extract the bounds of the array, then join that with generate_series to extract the values one by one, the index into the array being the row number.

Sample query assuming the table is scores(value int):

SELECT i AS row_number,arr[i] AS score
 FROM (SELECT arr,generate_series(1,nb) AS i
   FROM (SELECT arr,array_upper(arr,1) AS nb
     FROM (SELECT array(SELECT value FROM scores ORDER BY value DESC) AS arr
     ) AS s2
   ) AS s1
 ) AS s0
Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
0

Do you have a PK for this table?

Just self join and count items with: a higher or equal score and higher PK.

PK comparison will break ties and give you desired result.

And after you upgrade to 9.1 - use row_number().

Ihor Romanchenko
  • 26,995
  • 8
  • 48
  • 44