Postgres: Найдите позицию определенной строки в наборе результатов?

Я пытаюсь выяснить, как получить относительное положение одного элемента в запросе относительно всех элементов, возвращаемых из запроса.

Например, длинный способ получения ответа:

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

Есть ли простой способ получить required_value только через один запрос postgres?

Ответы

Ответ 1

Использовать аналитическую/ранжирующую/оконную функциональность - ссылку на документацию 8.4:

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

Альтернативный вариант без синтаксиса WITH:

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

Столбец position будет целочисленным значением, представляющим местоположение записи, где значение id равно 65, на основе столбца published_date в порядке возрастания. Если вы хотите, чтобы значение позиции дублировалось при наличии связей, замените ROW_NUMBER() на RANK()