Con MySQL, ¿cómo puedo generar una columna que contenga el índice del registro en una tabla?

¿Hay alguna manera de obtener el número de fila real de una consulta?

Quiero poder ordenar una tabla llamada league_girl por un campo llamado puntaje; y devolver el nombre de usuario y la posición real de la fila de ese nombre de usuario.

Quiero clasificar a los usuarios para poder saber dónde está un usuario en particular, es decir. Joe está en la posición 100 de 200, es decir

User Score Row
Joe  100    1
Bob  50     2
Bill 10     3

He visto algunas soluciones aquí, pero probé la mayoría y ninguna devuelve el número de fila.

He probado esto:

SELECT position, username, score
FROM (SELECT @row := @row + 1 AS position, username, score 
       FROM league_girl GROUP BY username ORDER BY score DESC)

Como derivado

...pero no parece devolver la posición de la fila.

¿Algunas ideas?

Jun 27 '10 16:06 TheBounder

Quizás quieras probar lo siguiente:

SELECT  l.position, 
        l.username, 
        l.score,
        @curRow := @curRow + 1 AS row_number
FROM    league_girl l
JOIN    (SELECT @curRow := 0) r;

La JOIN (SELECT @curRow := 0)pieza permite la inicialización de variables sin requerir un SETcomando separado.

Caso de prueba:

CREATE TABLE league_girl (position int, username varchar(10), score int);
INSERT INTO league_girl VALUES (1, 'a', 10);
INSERT INTO league_girl VALUES (2, 'b', 25);
INSERT INTO league_girl VALUES (3, 'c', 75);
INSERT INTO league_girl VALUES (4, 'd', 25);
INSERT INTO league_girl VALUES (5, 'e', 55);
INSERT INTO league_girl VALUES (6, 'f', 80);
INSERT INTO league_girl VALUES (7, 'g', 15);

Consulta de prueba:

SELECT  l.position, 
        l.username, 
        l.score,
        @curRow := @curRow + 1 AS row_number
FROM    league_girl l
JOIN    (SELECT @curRow := 0) r
WHERE   l.score > 50;

Resultado:

+----------+----------+-------+------------+
| position | username | score | row_number |
+----------+----------+-------+------------+
|        3 | c        |    75 |          1 |
|        5 | e        |    55 |          2 |
|        6 | f        |    80 |          3 |
+----------+----------+-------+------------+
3 rows in set (0.00 sec)

Jun 27 '2010 09:06 Daniel Vassallo

SELECT @i:=@i+1 AS iterator, t.*
FROM tablename t,(SELECT @i:=0) foo

Aug 12 '2010 17:08 Peter Johnson

Aquí viene la estructura de la plantilla que utilicé:

  select
          /*this is a row number counter*/
          ( select @rownum := @rownum + 1 from ( select @rownum := 0 ) d2 ) 
          as rownumber,
          d3.*
  from 
  ( select d1.* from table_name d1 ) d3

Y aquí está mi código de trabajo:

select     
           ( select @rownum := @rownum + 1 from ( select @rownum := 0 ) d2 ) 
           as rownumber,
           d3.*
from
(   select     year( d1.date ), month( d1.date ), count( d1.id )
    from       maindatabase d1
    where      ( ( d1.date >= '2013-01-01' ) and ( d1.date <= '2014-12-31' ) )
    group by   YEAR( d1.date ), MONTH( d1.date ) ) d3

Dec 12 '2014 21:12 goadreamer

También puedes usar

SELECT @curRow := ifnull(@curRow,0) + 1 Row, ...

para inicializar la variable del contador.

Aug 21 '2013 04:08 Hearth

Suponiendo que MySQL lo admita, puede hacerlo fácilmente con una subconsulta SQL estándar:

select 
    (count(*) from league_girl l1 where l2.score > l1.score and l1.id <> l2.id) as position,
    username,
    score
from league_girl l2
order by score;

Para grandes cantidades de resultados mostrados, esto será un poco lento y querrás cambiar a una unión automática.

Mar 23 '2012 15:03 ftzdomino