ROW NUMBER() en MySQL

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

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

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

Sí. Si fuera Oracle, podría usar la función LEAD para alcanzar el máximo en el siguiente valor. Afortunadamente, Quassnoi cubre la lógica de lo que necesita implementar en MySQL.

Siempre termino siguiendo este patrón. Teniendo en cuenta este cuadro:

+------+------+
|    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!

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

Echa un vistazo a este artículo, muestra cómo imitar SQL ROW_NUMBER() con una partición en MySQL. Me encontré con este mismo escenario en una implementación de WordPress. Necesitaba ROW_NUMBER () y no estaba allí.

Http://www.explodybits.com/2011/11/mysql-row-number /

El ejemplo en el artículo está usando una sola partición por campo. Para particionar por campos adicionales podrías hacer algo como esto:

  SELECT  @row_num := IF(@prev_value=concat_ws('',t.col1,t.col2),@row_num+1,1) AS RowNumber
         ,t.col1 
         ,t.col2
         ,t.Col3
         ,t.col4
         ,@prev_value := concat_ws('',t.col1,t.col2)
    FROM table1 t,
         (SELECT @row_num := 1) x,
         (SELECT @prev_value := '') y
   ORDER BY t.col1,t.col2,t.col3,t.col4 

Usando concat_ws maneja null. He probado esto contra 3 campos usando int, date y varchar. Espero que esto ayude. Echa un vistazo al artículo, ya que desglosa esta consulta y la explica.

También votaría por la solución de Mosty Mostacho con una pequeña modificación a su código de consulta:

SELECT a.i, a.j, (
    SELECT count(*) from test b where a.j >= b.j AND a.i = b.i
) AS row_number FROM test a

Que dará el mismo 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 |
+------+------+------------+

Para la tabla:

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

Con la única diferencia de que la consulta no usa UNIR y AGRUPAR POR, confiando en seleccionar anidado en su lugar.

Desde MySQL 8.0.0 y arriba se pueden usar funciones de ventana de forma nativa.

1.4 ¿Qué hay de Nuevo en MySQL 8.0:

Funciones de ventana.

MySQL ahora admite funciones de ventana que, para cada fila de una consulta, realizan un cálculo utilizando filas relacionadas con esa fila. Estas incluyen funciones como RANK (), LAG () y NTILE (). Además, varias funciones agregadas existentes ahora se pueden usar como funciones de ventana; por ejemplo, SUM () y AVG().

ROW_NUMBER () over_clause :

Devuelve el número de la fila actual dentro de su partición. Los números de filas van desde 1 hasta el número de filas de partición.

ORDER BY afecta al orden en el que se numeran las filas. Sin ORDEN POR, la numeración de filas es indeterminada.

Demo:

CREATE TABLE Table1(
  id INT AUTO_INCREMENT PRIMARY KEY, col1 INT,col2 INT, col3 TEXT);

INSERT INTO Table1(col1, col2, col3)
VALUES (1,1,'a'),(1,1,'b'),(1,1,'c'),
       (2,1,'x'),(2,1,'y'),(2,2,'z');

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

Demostración de DBFiddle

Definiría una función:

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

Entonces podría hacer:

select getFakeId() as id, t.* from table t, (select @fakeId:=0) as t2;

Ahora no tiene una subconsulta, que no puede tener en las vistas.

No hay función como rownum, row_num() en MySQL, pero el camino es como a continuación:

select 
      @s:=@s+1 serial_no, 
      tbl.* 
from my_table tbl, (select @s:=0) as s;

Consulta para row_number en mysql

set @row_number=0;
select (@row_number := @row_number +1) as num,id,name from sbs

La solución que encontré para funcionar mejor fue usar una subconsulta como esta:

SELECT 
    col1, col2, 
    (
        SELECT COUNT(*) 
        FROM Table1
        WHERE col1 = t1.col1
        AND col2 = t1.col2
        AND col3 > t1.col3
    ) AS intRow
FROM Table1 t1

La PARTICIÓN POR columnas simplemente se compara con '=' y se separa por Y. El ORDEN POR columnas se compararía con ' ', y se separaría por OR.

He encontrado que esto es muy flexible, incluso si es un poco costoso.

La funcionalidad rownumber no se puede imitar. Es posible que obtengas los resultados que esperas, pero lo más probable es que te decepciones en algún momento. Esto es lo que dice la documentación de mysql:

Para otras sentencias, como SELECT, puede obtener los resultados que espera, pero esto no está garantizado. En la siguiente declaración, podría pensar que MySQL evaluará @a primero y luego hará una tarea en segundo lugar: SELECCIONE @a, @a:=@a+1, ...; Sin embargo, el orden de evaluación de las expresiones la participación de variables de usuario no está definida.

Saludos, Georgi.

MariaDB 10.2 está implementando "Funciones de ventana", incluyendo RANK (), ROW_NUMBER() y varias otras cosas:

Https://mariadb.com/kb/en/mariadb/window-functions /

Basado en una charla en Percona Live este mes, están razonablemente bien optimizados.

La sintaxis es idéntica al código de la Pregunta.

Un poco tarde, pero también puede ayudar a alguien que busca respuestas...

Between rows / row_number example-consulta recursiva que se puede usar en cualquier SQL:

WITH data(row_num, some_val) AS 
(
 SELECT 1 row_num, 1 some_val FROM any_table --dual in Oracle
  UNION ALL
 SELECT row_num+1, some_val+row_num FROM data WHERE row_num < 20 -- any number
)
SELECT * FROM data
 WHERE row_num BETWEEN 5 AND 10
/

ROW_NUM    SOME_VAL
-------------------
5           11
6           16
7           22
8           29
9           37
10          46

Esto permite que la misma funcionalidad que proporciona ROW_NUMBER() Y PARTITION BY se logre en MySQL

SELECT  @row_num := IF(@prev_value=GENDER,@row_num+1,1) AS RowNumber
       FirstName, 
       Age,
       Gender,
       @prev_value := GENDER
  FROM Person,
      (SELECT @row_num := 1) x,
      (SELECT @prev_value := '') y
  ORDER BY Gender, Age DESC

También un poco tarde, pero hoy tenía la misma necesidad, así que hice una búsqueda en Google y, finalmente, un enfoque general simple que se encuentra aquí en el artículo de Pinal Dave http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/

Quería centrarme en la pregunta original de Pablo (que era mi problema también) así que resumo mi solución como un ejemplo de trabajo.

Debido a que queremos particionar sobre dos columnas, crearía una variable ESTABLECIDA durante el iteración para identificar si se inició un nuevo grupo.

SELECT col1, col2, col3 FROM (
  SELECT col1, col2, col3,
         @n := CASE WHEN @v = MAKE_SET(3, col1, col2)
                    THEN @n + 1 -- if we are in the same group
                    ELSE 1 -- next group starts so we reset the counter
                END AS row_number,
         @v := MAKE_SET(3, col1, col2) -- we store the current value for next iteration
    FROM Table1, (SELECT @n := 0, @v := NULL) r -- helper table for iteration with startup values
   ORDER BY col1, col2, col3 DESC -- because we want the row with maximum value
) x WHERE row_number = 1 -- and here we select exactly the wanted row from each group

El 3 significa en el primer parámetro de MAKE_SET que quiero tanto valor en el CONJUNTO (3=1/2). Por supuesto, si no tenemos dos o más columnas construyendo los grupos podemos eliminar la operación MAKE_SET. La construcción es exactamente la misma. Esto está funcionando para mí según sea necesario. Muchas gracias a Pinal Dave por su clara demostración.

No veo ninguna respuesta simple que cubra la parte "PARTICIÓN POR", así que aquí está la mía:

SELECT
    *
FROM (
    select
        CASE WHEN @partitionBy_1 = l THEN @row_number:=@row_number+1 ELSE @row_number:=1 END AS i
        , @partitionBy_1:=l AS p
        , t.*
    from (
        select @row_number:=0,@partitionBy_1:=null
    ) as x
    cross join (
        select 1 as n, 'a' as l
        union all
        select 1 as n, 'b' as l    
        union all
        select 2 as n, 'b' as l    
        union all
        select 2 as n, 'a' as l
        union all
        select 3 as n, 'a' as l    
        union all    
        select 3 as n, 'b' as l    
    ) as t
    ORDER BY l, n
) AS X
where i > 1

• La cláusula ORDER BY debe reflejar su necesidad de ROW_NUMBER. Por lo tanto, ya hay una limitación clara: no puede tener varios ROW_NUMBER "emulación" de este formulario al mismo tiempo.
• El orden de la "columna calculada" importa. Si mysql calcula esas columnas en otro orden, es posible que no funcione.

• En este simple ejemplo solo pongo uno pero puedes tener varias partes" PARTICIÓN POR "

      CASE WHEN @partitionBy_1 = part1 AND @partitionBy_2 = part2 [...] THEN @row_number:=@row_number+1 ELSE @row_number:=1 END AS i
      , @partitionBy_1:=part1 AS P1
      , @partitionBy_2:=part2 AS P2
      [...] 
  FROM (
      SELECT @row_number:=0,@partitionBy_1:=null,@partitionBy_2:=null[...]
  ) as x
  

Esto también podría ser una solución:

SET @row_number = 0;

SELECT 
    (@row_number:=@row_number + 1) AS num, firstName, lastName
FROM
    employees

set @i = 1;  
INSERT INTO ARG_VALUE_LOOKUP(ARG_VALUE_LOOKUP_ID,ARGUMENT_NAME,VALUE,DESCRIPTION,UPDATE_TIMESTAMP,UPDATE_USER,VER_NBR,OBJ_ID) 
select @i:= @i+1 as ARG_VALUE_LOOKUP_ID,ARGUMENT_NAME,VALUE,DESCRIPTION,CURRENT_TIMESTAMP,'admin',1,UUID() 
FROM TEMP_ARG_VALUE_LOOKUP 
order by ARGUMENT_NAME;

Esto funciona perfectamente para mí para crear RowNumber cuando tenemos más de una columna. En este caso dos columnas.

SELECT @row_num := IF(@prev_value= concat(`Fk_Business_Unit_Code`,`NetIQ_Job_Code`), @row_num+1, 1) AS RowNumber, 
    `Fk_Business_Unit_Code`,   
    `NetIQ_Job_Code`,  
    `Supervisor_Name`,  
    @prev_value := concat(`Fk_Business_Unit_Code`,`NetIQ_Job_Code`)  
FROM (SELECT DISTINCT `Fk_Business_Unit_Code`,`NetIQ_Job_Code`,`Supervisor_Name`         
      FROM Employee    
      ORDER BY `Fk_Business_Unit_Code`, `NetIQ_Job_Code`, `Supervisor_Name` DESC) z,  
(SELECT @row_num := 1) x,  
(SELECT @prev_value := '') y  
ORDER BY `Fk_Business_Unit_Code`, `NetIQ_Job_Code`,`Supervisor_Name` DESC

SELECT 
    col1, col2, 
    count(*) as intRow
FROM Table1
GROUP BY col1,col2
ORDER BY col3 desc