registros - Numeración de filas en PostgreSQL

row_number in postgresql example

Question

¿Cómo obtener el número de fila en PostgreSQL cuando los resultados están ordenados por alguna columna?

p.ej

SELECT 30+row_number() AS position, *
FROM users
ORDER BY salary DESC
LIMIT 30
OFFSET 30

Supuse que la consulta devolvería una lista como esta:

position | name | salary
31 | Joy | 4500
32 | Katie| 4000
33 | Frank| 3500

En realidad, tengo que duplicar la cláusula ORDER en la consulta para que sea funcional:

SELECT 30+row_number(ORDER BY salary DESC) AS position, *
FROM users
ORDER BY salary DESC
LIMIT 30
OFFSET 30

¿Hay alguna otra manera de devolver resultados ordenados y numerados sin necesidad de duplicar el código?

Sé que esto se puede resolver incrementando alguna variable en la aplicación en sí, pero quiero hacer esto en la capa de la base de datos y regresar a la aplicación con los resultados ya numerados ...

Answer 1

no: el order by en la función de ventana y la cláusula order by de la declaración de select son funcionalmente dos cosas diferentes.

Además, su declaración produce: ERROR: window function call requires an OVER clause , por lo que:

SELECT 30+row_number(ORDER BY salary DESC) AS position, * FROM users ORDER BY salary DESC LIMIT 30 OFFSET 30

debiera ser:

SELECT 30+row_number() OVER(ORDER BY salary DESC) AS position, * FROM users ORDER BY salary DESC LIMIT 30 OFFSET 30

Tenga en cuenta que si los salarios no son únicos, entonces no hay garantía de que incluso produzcan el mismo pedido. Quizás sería mejor hacer:

SELECT *
FROM ( SELECT 30+row_number() OVER(ORDER BY salary DESC) AS position, *
FROM users )
ORDER BY position LIMIT 30 OFFSET 30

También tenga en cuenta que si está ejecutando esta consulta varias veces con diferentes compensaciones, necesita:

1. establece tu nivel de aislamiento en serializable
2. Asegúrese de que todo lo que está ordenando es único

o puede obtener duplicados y filas faltantes. Vea los comentarios en esta respuesta para saber por qué.