sql - ROW_NUMBER() in MySQL

ID : 10009

viewed : 39

Tags : mysqlgreatest-n-per-grouprankrow-numbermysql

Top 5 Answer for sql - ROW_NUMBER() in MySQL

vote vote

99

There is no ranking functionality in MySQL. The closest you can get is to use a variable:

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

so how would that work in my case? I'd need two variables, one for each of col1 and col2? Col2 would need resetting somehow when col1 changed..?

Yes. If it were Oracle, you could use the LEAD function to peak at the next value. Thankfully, Quassnoi covers the logic for what you need to implement in MySQL.

vote vote

83

I want the row with the single highest col3 for each (col1, col2) pair.

That's a groupwise maximum, one of the most commonly-asked SQL questions (since it seems like it should be easy, but actually it kind of isn't).

I often plump for a null-self-join:

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; 

“Get the rows in the table for which no other row with matching col1,col2 has a higher col3.” (You will notice this and most other groupwise-maximum solutions will return multiple rows if more than one row has the same col1,col2,col3. If that's a problem you may need some post-processing.)

vote vote

79

I always end up following this pattern. Given this table:

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

You can get this result:

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

By running this query, which doesn't need any variable defined:

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 

Hope that helps!

vote vote

67

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

50

From MySQL 8.0.0 and above you could natively use windowed functions.

1.4 What Is New in MySQL 8.0:

Window functions.

MySQL now supports window functions that, for each row from a query, perform a calculation using rows related to that row. These include functions such as RANK(), LAG(), and NTILE(). In addition, several existing aggregate functions now can be used as window functions; for example, SUM() and AVG().

ROW_NUMBER() over_clause :

Returns the number of the current row within its partition. Rows numbers range from 1 to the number of partition rows.

ORDER BY affects the order in which rows are numbered. Without ORDER BY, row numbering is indeterminate.

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; 

DBFiddle Demo

Top 3 video Explaining sql - ROW_NUMBER() in MySQL

Related QUESTION?