MySQL obtient la position d'une ligne dans ORDER BY

Question

Avec la table MySQL suivante :

+-----------------------------+
+ id INT UNSIGNED             +
+ name VARCHAR(100)           +
+-----------------------------+

Comment puis-je sélectionner un simple ET sa position parmi les autres lignes du tableau, lorsqu'elle est triée par name ASC . Donc si les données de la table ressemblent à ceci, lorsqu'elles sont triées par nom :

+-----------------------------+
+ id | name                   +
+-----------------------------+
+  5 | Alpha                  +
+  7 | Beta                   +
+  3 | Delta                  +
+ .....                       +
+  1 | Zed                    +
+-----------------------------+

Comment pourrais-je sélectionner le Beta pour obtenir la position actuelle de cette ligne ? L'ensemble de résultats que je recherche serait quelque chose comme ceci :

+-----------------------------+
+ id | position | name        +
+-----------------------------+
+  7 |        2 | Beta        +
+-----------------------------+

Je peux faire un simple SELECT * FROM tbl ORDER BY name ASC puis énumérer les lignes en PHP, mais il semble inutile de charger un jeu de résultats potentiellement important pour une seule ligne.

Answer 1

Utilisez ça :

SELECT x.id, 
       x.position,
       x.name
  FROM (SELECT t.id,
               t.name,
               @rownum := @rownum + 1 AS position
          FROM TABLE t
          JOIN (SELECT @rownum := 0) r
      ORDER BY t.name) x
 WHERE x.name = 'Beta'

...pour obtenir une valeur de position unique. Ceci :

SELECT t.id,
       (SELECT COUNT(*)
          FROM TABLE x
         WHERE x.name <= t.name) AS position,
       t.name    
  FROM TABLE t      
 WHERE t.name = 'Beta'

...donnera aux égalités la même valeur. Par exemple, s'il y a deux valeurs en deuxième position, elles auront toutes deux une position de 2 alors que la première requête donnera une position de 2 à l'une d'entre elles, et de 3 à l'autre...

Answer 2

C'est le seul moyen auquel je pense :

SELECT `id`,
       (SELECT COUNT(*) FROM `table` WHERE `name` <= 'Beta') AS `position`,
       `name`
FROM `table`
WHERE `name` = 'Beta'

Answer 3

Si la requête est simple et que la taille du jeu de résultats renvoyés est potentiellement importante, vous pouvez essayer de la diviser en deux requêtes.

La première requête avec un critère de filtrage réduit ne vise qu'à récupérer les données de cette rangée, et la seconde requête utilise COUNT avec WHERE pour calculer la position.

Par exemple, dans votre cas

Question 1 :

SELECT * FROM tbl WHERE name = 'Beta'

Requête 2 :

SELECT COUNT(1) FROM tbl WHERE name >= 'Beta'

Nous utilisons cette approche dans une table avec 2M d'enregistrements et c'est beaucoup plus évolutif que l'approche d'OMG Ponies.

Answer 4

La position d'une ligne dans le tableau représente le nombre de lignes qui sont "meilleures" que la ligne ciblée.

Vous devez donc compter ces rangées.

SELECT COUNT(*)+1 FROM table OÙ name < "Beta

En cas d'égalité, la position la plus élevée est restituée.

Si vous ajoutez une autre ligne avec le même nom de "Beta" après la ligne "Beta" existante, la position retournée sera toujours 2, car elles partageront la même place dans la classification.

J'espère que cela aidera les personnes qui chercheront quelque chose de similaire à l'avenir, car je pense que le propriétaire de la question a déjà résolu son problème.

Answer 5

J'étais moi aussi bloqué sur ce point, mais après avoir consacré du temps et des efforts à la recherche sur SO, j'ai trouvé ma réponse grâce à ceci : Obtenir la position d'une ligne dans une requête MYSQL