268 votos

ROW_NUMBER() en MySQL

¿Hay alguna forma agradable en MySQL de replicar la función de MS Sql Server ROW_NUMBER() ?

Por ejemplo:

SELECT 
    col1, col2, 
    ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow
FROM Table1

Entonces podría, por ejemplo, añadir una condición para limitar intRow a 1 para obtener una sola fila con la más alta col3 para cada uno (col1, col2) par.

198voto

OMG Ponies Puntos 144785

No hay ninguna funcionalidad de clasificación en MySQL. Lo más cercano que puedes conseguir es usar una variable:

SELECT t.*, 
       @rownum := @rownum + 1 AS rank
  FROM YOUR_TABLE t, 
       (SELECT @rownum := 0) r

así que, ¿cómo funcionaría eso en mi caso? Necesitaría dos variables, una para cada uno de los col1 y col2. ¿Col2 necesitaría ser reajustada de alguna manera cuando col1 cambiara ?

Sí. Si fuera el Oráculo, podrías usar la función LEAD para llegar al siguiente valor. Afortunadamente, Quassnoi cubre la lógica para lo que necesitas implementar en MySQL .

99voto

bobince Puntos 270740

Quiero la fila con el col3 más alto para cada par (col1, col2).

Eso es un máximo en grupo una de las preguntas SQL más frecuentes (ya que parece que debería ser fácil, pero en realidad no lo es).

A menudo me gusta la unión de un nulo:

SELECT t0.col3
FROM table AS t0
LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3
WHERE t1.col1 IS NULL;

"Consigue las filas de la tabla para las que ninguna otra fila con col1, col2 tiene un col3 más alto". (Notarán que ésta y la mayoría de las otras soluciones de grupo máximo devolverán múltiples filas si más de una fila tiene el mismo col1,col2,col3. Si eso es un problema, puede que necesites algún tipo de post-procesamiento).

80voto

Mosty Mostacho Puntos 22803

Siempre termino siguiendo este patrón. Dada esta tabla:

+------+------+
|    i |    j |
+------+------+
|    1 |   11 |
|    1 |   12 |
|    1 |   13 |
|    2 |   21 |
|    2 |   22 |
|    2 |   23 |
|    3 |   31 |
|    3 |   32 |
|    3 |   33 |
|    4 |   14 |
+------+------+

Puedes obtener este resultado:

+------+------+------------+
|    i |    j | row_number |
+------+------+------------+
|    1 |   11 |          1 |
|    1 |   12 |          2 |
|    1 |   13 |          3 |
|    2 |   21 |          1 |
|    2 |   22 |          2 |
|    2 |   23 |          3 |
|    3 |   31 |          1 |
|    3 |   32 |          2 |
|    3 |   33 |          3 |
|    4 |   14 |          1 |
+------+------+------------+

Ejecutando esta consulta, que no necesita ninguna variable definida:

SELECT a.i, a.j, count(*) as row_number FROM test a
JOIN test b ON a.i = b.i AND a.j >= b.j
GROUP BY a.i, a.j

¡Espero que eso ayude!

58voto

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

12voto

Quincy Puntos 448

Yo definiría una función:

delimiter 
DROP FUNCTION `getFakeId`
CREATE FUNCTION `getFakeId`() RETURNS int(11)
    DETERMINISTIC<br>
begin<br>
return if(@fakeId, @fakeId:=@fakeId+1, @fakeId:=1);
end

entonces podría hacerlo:

select getFakeId() as id, t.* from table t;

Ahora no tienes una subconsulta, que no puedes tener a la vista.

Iteramos.com

Iteramos es una comunidad de desarrolladores que busca expandir el conocimiento de la programación mas allá del inglés.
Tenemos una gran cantidad de contenido, y también puedes hacer tus propias preguntas o resolver las de los demás.

Powered by:

X