ti-enxame.com

MySQL obtém posição de linha em ORDER BY

Com a seguinte tabela MySQL:

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

Como posso selecionar uma linha única E sua posição entre as outras linhas da tabela, quando classificadas por name ASC. Portanto, se os dados da tabela estiverem assim, quando classificados por nome:

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

Como eu poderia selecionar a linha Beta obtendo a posição atual dessa linha? O conjunto de resultados que estou procurando seria algo como isto:

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

Eu posso fazer um simples SELECT * FROM tbl ORDER BY name ASC e depois enumerar as linhas no PHP, mas parece um desperdício carregar um conjunto de resultados potencialmente grande apenas para uma única linha.

77
leepowers

Usa isto:

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'

... para obter um valor de posição exclusivo. Este:

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

... dará aos laços o mesmo valor. IE: Se houver dois valores no segundo lugar, ambos terão uma posição 2 quando a primeira consulta fornecerá uma posição 2 para um deles e 3 para o outro ...

108
OMG Ponies

Esta é a única maneira que consigo pensar:

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

Se a consulta for simples e o tamanho do conjunto de resultados retornado for potencialmente grande, tente dividi-la em duas consultas.

A primeira consulta com um critério de filtragem restrito apenas para recuperar dados dessa linha e a segunda consulta usa a cláusula COUNT com WHERE para calcular a posição.

Por exemplo no seu caso

Consulta 1:

SELECT * FROM tbl WHERE name = 'Beta'

Consulta 2:

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

Usamos essa abordagem em uma tabela com registro de 2M e isso é muito mais escalável do que a abordagem da OMG Ponies.

8
Max

Eu tenho um problema muito parecido, é por isso que não vou fazer a mesma pergunta, mas vou compartilhar aqui o que fiz, tive que usar também um grupo e pedir pelo AVG. Existem estudantes, com assinaturas e socore, e eu tive que classificá-los (em outras palavras, primeiro calculei o AVG, depois os ordenei em DESC e, finalmente, precisei adicionar a posição (classificação para mim), então fiz algo Muito parecido como o melhor resposta aqui, com algumas pequenas mudanças que se ajustam ao meu problema):

Finalmente coloquei a coluna position (classificação para mim) no SELECT externo

SET @rank=0;
SELECT @rank := @rank + 1 AS ranking, t.avg, t.name
  FROM(SELECT avg(students_signatures.score) as avg, students.name as name
FROM alumnos_materia
JOIN (SELECT @rownum := 0) r
left JOIN students ON students.id=students_signatures.id_student
GROUP BY students.name order by avg DESC) t 
2
Damián Rafael Lattenero

A posição de uma linha na tabela representa quantas linhas são "melhores" que a linha de destino.

Então, você deve contar essas linhas.

SELECIONE A CONTAGEM (*) + 1 DE table WHERE name <'Beta'

Em caso de empate, a posição mais alta é retornada.

Se você adicionar outra linha com o mesmo nome de "Beta" após a linha "Beta" existente, a posição retornada ainda será 2, pois compartilhariam o mesmo lugar na classificação.

Espero que isso ajude as pessoas que procurarão algo semelhante no futuro, pois acredito que o proprietário da pergunta já resolveu seu problema.

1
NVG

As outras respostas parecem muito complicadas para mim.

Aí vem um exemplo fácil , digamos que você tenha uma tabela com colunas:

userid | points

e você deseja classificar os IDs do usuário por pontos e obter a posição da linha (a "classificação" do usuário), use:

SET @row_number = 0;

SELECT 
    (@row_number:[email protected]_number + 1) AS num, userid, points
FROM
    ourtable
ORDER BY points DESC

num fornece a posição da linha (classificação).

Se você possui o MySQL 8.0+, poderá usar ROW_NUMBER ()

1
Kai Noack

Eu estava passando pela resposta aceita e parecia um pouco complicado, então aqui está a versão simplificada dela.

SELECT t,COUNT(*) AS position FROM t      
 WHERE name <= 'search string' ORDER BY name
0
Davinder Singh