Postgres: Find position of a specific row within a resultset?

Question

Hello,

I'm trying to figure out how to get the relative position of a single item in a query relative to all the items returned from the query.

For example,the long hand way of getting the answer would be:

single_item = SELECT * FROM table WHERE id=65
result = SELECT * FROM table WHERE published_date < date_value
x=1
foreach(result as item):
    if(item.id == single_item.id):
        required_value = x
    endif
    x++
endforeach

Is there a simple way of getting required_value just through a single postgres query?

Answer 1

Use analytic/ranking/windowing functionality - 8.4 documentation link:

WITH summary AS (
   SELECT t.*,
          ROW_NUMBER() OVER(ORDER BY t.published_date) AS position
     FROM TABLE t)
SELECT s.*
  FROM summary s
 WHERE s.id = 65

Alternate without the WITH syntax:

SELECT s.*
  FROM (SELECT t.*,
               ROW_NUMBER() OVER(ORDER BY t.published_date) AS position
          FROM TABLE t) s
 WHERE s.id = 65

The position column will be an integer value representing the location of the record where the id value is 65, based on the published_date column in ascending order. If you want the position value to be duplicated when there are ties, replace ROW_NUMBER() with RANK()