Postgres: Find position of a specific row within a resultset?
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 = 65Alternate 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 = 65The 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()