Problem
Is it possible to recreate the SQL Server function ROW NUMBER() in MySQL?
For example:
SELECT
col1, col2,
ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow
FROM Table1
Then, for each (col1, col2) pair, I could add a condition to limit intRow to 1 to produce a single row with the highest col3.
Asked by Paul
Solution #1
MySQL does not have a ranking feature. The only way to get close is to utilize a variable:
SELECT t.*,
@rownum := @rownum + 1 AS rank
FROM YOUR_TABLE t,
(SELECT @rownum := 0) r
Yes. You might utilize the LEAD function in Oracle to peek at the following value. Quassnoi, thankfully, outlines the reasoning for what you’ll need to do with MySQL.
Answered by OMG Ponies
Solution #2
That’s a groupwise maximum, which is one of the most often asked SQL questions (because it appears to be simple but isn’t).
I frequently choose 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 table rows for which no other row with the same col1,col2 has a higher col3.” (You’ll notice that if more than one row has the same col1,col2,col3, this and most other groupwise-maximum solutions will return numerous rows.) If that’s a problem you may need some post-processing.)
Answered by bobince
Solution #3
This is the cycle I always fall into. Given the following 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 achieve the following 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 the following query, which does not require any variables to be 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!
Answered by Mosty Mostacho
Solution #4
SELECT
@i:=@i+1 AS iterator,
t.*
FROM
tablename AS t,
(SELECT @i:=0) AS foo
Answered by Peter Johnson
Solution #5
Windowed functions were available natively in MySQL 8.0.0 and later.
1.4 MySQL 8.0: What’s New:
ROW_NUMBER() over_clause :
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
Answered by Lukasz Szozda
Post is based on https://stackoverflow.com/questions/1895110/row-number-in-mysql