Función Rank en MySQL

Aquí hay una solución genérica que ordena una tabla basada en una columna y asigna rango; a las filas con lazos se les asigna el mismo rango (usa una variable adicional para este propósito):

SET @prev_value = NULL;
SET @rank_count = 0;
SELECT id, rank_column, CASE
    WHEN @prev_value = rank_column THEN @rank_count
    WHEN @prev_value := rank_column THEN @rank_count := @rank_count + 1
END AS rank
FROM rank_table
ORDER BY rank_column

Tenga en cuenta que hay dos instrucciones de asignación en la segunda cláusula WHEN. Datos de la muestra:

CREATE TABLE rank_table(id INT, rank_column INT);
INSERT INTO rank_table (id, rank_column) VALUES
(1, 10),
(2, 20),
(3, 30),
(4, 30),
(5, 30),
(6, 40),
(7, 50),
(8, 50),
(9, 50);

Salida:

+------+-------------+------+
| id   | rank_column | rank |
+------+-------------+------+
|    1 |          10 |    1 |
|    2 |          20 |    2 |
|    3 |          30 |    3 |
|    4 |          30 |    3 |
|    5 |          30 |    3 |
|    6 |          40 |    4 |
|    7 |          50 |    5 |
|    8 |          50 |    5 |
|    9 |          50 |    5 |
+------+-------------+------+

Violín SQL

Mientras que la respuesta más votada clasifica, no particiona, puede hacer un auto Join para obtener la partición completa también:

SELECT    a.first_name,
      a.age,
      a.gender,
        count(b.age)+1 as rank
FROM  person a left join person b on a.age>b.age and a.gender=b.gender 
group by  a.first_name,
      a.age,
      a.gender

Caso De Uso

CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));

INSERT INTO person VALUES (1, 'Bob', 25, 'M');
INSERT INTO person VALUES (2, 'Jane', 20, 'F');
INSERT INTO person VALUES (3, 'Jack', 30, 'M');
INSERT INTO person VALUES (4, 'Bill', 32, 'M');
INSERT INTO person VALUES (5, 'Nick', 22, 'M');
INSERT INTO person VALUES (6, 'Kathy', 18, 'F');
INSERT INTO person VALUES (7, 'Steve', 36, 'M');
INSERT INTO person VALUES (8, 'Anne', 25, 'F');

Respuesta:

Bill    32  M   4
Bob     25  M   2
Jack    30  M   3
Nick    22  M   1
Steve   36  M   5
Anne    25  F   3
Jane    20  F   2
Kathy   18  F   1

Un ajuste de la versión de Daniel para calcular el percentil junto con el rango. También dos personas con las mismas marcas obtendrán el mismo rango.

set @totalStudents = 0;
select count(*) into @totalStudents from marksheets;
SELECT id, score, @curRank := IF(@prevVal=score, @curRank, @studentNumber) AS rank, 
@percentile := IF(@prevVal=score, @percentile, (@totalStudents - @studentNumber + 1)/(@totalStudents)*100),
@studentNumber := @studentNumber + 1 as studentNumber, 
@prevVal:=score
FROM marksheets, (
SELECT @curRank :=0, @prevVal:=null, @studentNumber:=1, @percentile:=100
) r
ORDER BY score DESC

Resultados de la consulta de datos de muestra -

+----+-------+------+---------------+---------------+-----------------+
| id | score | rank | percentile    | studentNumber | @prevVal:=score |
+----+-------+------+---------------+---------------+-----------------+
| 10 |    98 |    1 | 100.000000000 |             2 |              98 |
|  5 |    95 |    2 |  90.000000000 |             3 |              95 |
|  6 |    91 |    3 |  80.000000000 |             4 |              91 |
|  2 |    91 |    3 |  80.000000000 |             5 |              91 |
|  8 |    90 |    5 |  60.000000000 |             6 |              90 |
|  1 |    90 |    5 |  60.000000000 |             7 |              90 |
|  9 |    84 |    7 |  40.000000000 |             8 |              84 |
|  3 |    83 |    8 |  30.000000000 |             9 |              83 |
|  4 |    72 |    9 |  20.000000000 |            10 |              72 |
|  7 |    60 |   10 |  10.000000000 |            11 |              60 |
+----+-------+------+---------------+---------------+-----------------+

Combinación de la respuesta de Daniel y Salman. Sin embargo el rango no dará como continúa la secuencia con los lazos existe . En su lugar se salta el rango a la siguiente. Por lo tanto, el máximo siempre alcanza el recuento de filas.

    SELECT    first_name,
              age,
              gender,
              IF(age=@_last_age,@curRank:=@curRank,@curRank:=@_sequence) AS rank,
              @_sequence:=@_sequence+1,@_last_age:=age
    FROM      person p, (SELECT @curRank := 1, @_sequence:=1, @_last_age:=0) r
    ORDER BY  age;

Esquema y Caso de prueba:

CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));

INSERT INTO person VALUES (1, 'Bob', 25, 'M');
INSERT INTO person VALUES (2, 'Jane', 20, 'F');
INSERT INTO person VALUES (3, 'Jack', 30, 'M');
INSERT INTO person VALUES (4, 'Bill', 32, 'M');
INSERT INTO person VALUES (5, 'Nick', 22, 'M');
INSERT INTO person VALUES (6, 'Kathy', 18, 'F');
INSERT INTO person VALUES (7, 'Steve', 36, 'M');
INSERT INTO person VALUES (8, 'Anne', 25, 'F');
INSERT INTO person VALUES (9, 'Kamal', 25, 'M');
INSERT INTO person VALUES (10, 'Saman', 32, 'M');

Salida:

+------------+------+--------+------+--------------------------+-----------------+
| first_name | age  | gender | rank | @_sequence:=@_sequence+1 | @_last_age:=age |
+------------+------+--------+------+--------------------------+-----------------+
| Kathy      |   18 | F      |    1 |                        2 |              18 |
| Jane       |   20 | F      |    2 |                        3 |              20 |
| Nick       |   22 | M      |    3 |                        4 |              22 |
| Kamal      |   25 | M      |    4 |                        5 |              25 |
| Anne       |   25 | F      |    4 |                        6 |              25 |
| Bob        |   25 | M      |    4 |                        7 |              25 |
| Jack       |   30 | M      |    7 |                        8 |              30 |
| Bill       |   32 | M      |    8 |                        9 |              32 |
| Saman      |   32 | M      |    8 |                       10 |              32 |
| Steve      |   36 | M      |   10 |                       11 |              36 |
+------------+------+--------+------+--------------------------+-----------------+

@Sam, tu punto es excelente en concepto, pero creo que malinterpretaste lo que los documentos de MySQL están diciendo en la página de referencia -- o malinterpreté: -) and y solo quería agregar esto para que si alguien se siente incómodo con la respuesta de @Daniel se sienta más tranquilo o al menos profundice un poco más.

Usted ve el "@curRank := @curRank + 1 COMO rango" dentro de la SELECCIÓN no es "una declaración", es una parte "atómica" de la declaración por lo que debe ser seguro.

El el documento al que hace referencia muestra ejemplos donde la misma variable definida por el usuario en 2 partes (atómicas) de la instrucción, por ejemplo, "SELECT @curRank, @curRank := @curRank + 1 AS rank".

Uno podría argumentar que @curRank se usa dos veces en la respuesta de @Daniel: (1) el" @curRank := @curRank + 1 COMO rango "y (2) el" (SELECT @curRank := 0) r " pero ya que el segundo uso es parte de la cláusula FROM, estoy bastante seguro de que está garantizado para ser evaluado primero; esencialmente lo que es un segundo, y precedente, declaración.

De hecho, en esa misma página de MySQL docs a la que hizo referencia, verá la misma solución en los comentarios could podría ser de donde lo sacó @Daniel; sí, sé que son los comentarios, pero son los comentarios en la página oficial de documentos y eso tiene algo de peso.

Si desea clasificar a una sola persona, puede hacer lo siguiente:

SELECT COUNT(Age) + 1
 FROM PERSON
WHERE(Age < age_to_rank)

Esta clasificación corresponde a la función de RANGO de oracle (Donde si tienes personas con la misma edad obtienen el mismo rango, y la clasificación después de eso no es consecutiva).

Es un poco más rápido que usar una de las soluciones anteriores en una subconsulta y seleccionar de esa para obtener el ranking de una persona.

Esto se puede usar para clasificar a todos, pero es más lento que las soluciones anteriores.

SELECT
  Age AS age_var,
(
  SELECT COUNT(Age) + 1
  FROM Person
  WHERE (Age < age_var)
 ) AS rank
 FROM Person

La solución más sencilla para determinar el rango de un valor dado es contar el número de valores antes de. Supongamos que tenemos los siguientes valores:

10 20 30 30 30 40

• Todos los 30 valores se consideran 3rd
• Todos los valores 40 se consideran 6º (rango) o 4º (rango denso)

Ahora volvamos a la pregunta original. Aquí hay algunos datos de muestra que se ordenan como se describe en OP (los rangos esperados se agregan en la derecha):

+------+-----------+------+--------+    +------+------------+
| id   | firstname | age  | gender |    | rank | dense_rank |
+------+-----------+------+--------+    +------+------------+
|   11 | Emily     |   20 | F      |    |    1 |          1 |
|    3 | Grace     |   25 | F      |    |    2 |          2 |
|   20 | Jill      |   25 | F      |    |    2 |          2 |
|   10 | Megan     |   26 | F      |    |    4 |          3 |
|    8 | Lucy      |   27 | F      |    |    5 |          4 |
|    6 | Sarah     |   30 | F      |    |    6 |          5 |
|    9 | Zoe       |   30 | F      |    |    6 |          5 |
|   14 | Kate      |   35 | F      |    |    8 |          6 |
|    4 | Harry     |   20 | M      |    |    1 |          1 |
|   12 | Peter     |   20 | M      |    |    1 |          1 |
|   13 | John      |   21 | M      |    |    3 |          2 |
|   16 | Cole      |   25 | M      |    |    4 |          3 |
|   17 | Dennis    |   27 | M      |    |    5 |          4 |
|    5 | Scott     |   30 | M      |    |    6 |          5 |
|    7 | Tony      |   30 | M      |    |    6 |          5 |
|    2 | Matt      |   31 | M      |    |    8 |          6 |
|   15 | James     |   32 | M      |    |    9 |          7 |
|    1 | Adams     |   33 | M      |    |   10 |          8 |
|   18 | Smith     |   35 | M      |    |   11 |          9 |
|   19 | Zack      |   35 | M      |    |   11 |          9 |
+------+-----------+------+--------+    +------+------------+

Para calcular RANK() OVER (PARTITION BY Gender ORDER BY Age) para Sarah, usted puede utilizar esta consulta:

SELECT COUNT(id) + 1 AS rank, COUNT(DISTINCT age) + 1 AS dense_rank
FROM testdata
WHERE gender = (SELECT gender FROM testdata WHERE id = 6)
AND age < (SELECT age FROM testdata WHERE id = 6)

+------+------------+
| rank | dense_rank |
+------+------------+
|    6 |          5 |
+------+------------+

Para calcular RANK() OVER (PARTITION BY Gender ORDER BY Age) para Todos filas usted puede utilizar esta consulta:

SELECT testdata.id, COUNT(lesser.id) + 1 AS rank, COUNT(DISTINCT lesser.age) + 1 AS dense_rank
FROM testdata
LEFT JOIN testdata AS lesser ON lesser.age < testdata.age AND lesser.gender = testdata.gender
GROUP BY testdata.id

Y aquí está el resultado (los valores unidos se agregan a la derecha):

+------+------+------------+    +-----------+-----+--------+
| id   | rank | dense_rank |    | firstname | age | gender |
+------+------+------------+    +-----------+-----+--------+
|   11 |    1 |          1 |    | Emily     |  20 | F      |
|    3 |    2 |          2 |    | Grace     |  25 | F      |
|   20 |    2 |          2 |    | Jill      |  25 | F      |
|   10 |    4 |          3 |    | Megan     |  26 | F      |
|    8 |    5 |          4 |    | Lucy      |  27 | F      |
|    6 |    6 |          5 |    | Sarah     |  30 | F      |
|    9 |    6 |          5 |    | Zoe       |  30 | F      |
|   14 |    8 |          6 |    | Kate      |  35 | F      |
|    4 |    1 |          1 |    | Harry     |  20 | M      |
|   12 |    1 |          1 |    | Peter     |  20 | M      |
|   13 |    3 |          2 |    | John      |  21 | M      |
|   16 |    4 |          3 |    | Cole      |  25 | M      |
|   17 |    5 |          4 |    | Dennis    |  27 | M      |
|    5 |    6 |          5 |    | Scott     |  30 | M      |
|    7 |    6 |          5 |    | Tony      |  30 | M      |
|    2 |    8 |          6 |    | Matt      |  31 | M      |
|   15 |    9 |          7 |    | James     |  32 | M      |
|    1 |   10 |          8 |    | Adams     |  33 | M      |
|   18 |   11 |          9 |    | Smith     |  35 | M      |
|   19 |   11 |          9 |    | Zack      |  35 | M      |
+------+------+------------+    +-----------+-----+--------+

A partir de MySQL 8, finalmente puede usar funciones de ventana también en MySQL: https://dev.mysql.com/doc/refman/8.0/en/window-functions.html

Su consulta se puede escribir exactamente de la misma manera:

SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS `Partition by Gender`, 
  FirstName, 
  Age,
  Gender 
FROM Person