Numérotation des lignes dans PostgreSQL

comment obtenir le nombre de ligne dans PostgreSQL quand les résultats sont ordonnés par une colonne quelconque?

p.ex.

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

j'ai supposé que la requête retournerait la liste comme ceci:

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

en fait je dois dupliquer la clause ORDER dans la requête pour la rendre fonctionnelle:

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

y a-t-il un autre moyen de rendre des résultats ordonnés et numérotés sans nécessité de dupliquer le code?

je sais que cela peut être résolu en incrémentant une variable dans l'application elle-même, mais je veux le faire à la couche de base de données et retourner à l'application déjà numérotés résultats...

27
demandé sur Troy Alford 2011-04-04 15:01:04

1 réponses

non - le order by dans la fonction fenêtrage et la clause order by de l'énoncé select sont deux choses fonctionnellement différentes.

aussi, votre déclaration produit: ERROR: window function call requires an OVER clause , donc:

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

devrait être:

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

notez que si les salaires ne sont pas uniques, il n'y a aucune garantie qu'ils produiront même le même ordre. Peut-être serait-il préférable de faire:

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

notez Également que si vous exécutez cette requête plusieurs fois avec différents décalages, vous devez:

  1. réglez votre niveau d'isolation à sérialisable
  2. assurez-vous que ce que vous commandez est unique

ou vous pouvez obtenir des doublons et des lignes manquantes. Voir les commentaires sur cette réponse pour pourquoi.

37
répondu Community 2017-05-23 12:00:17