MySQL 8.0.0 and above you could natively use windowed functions.
1.4 What Is New in MySQL 8.0:
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.
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;