Is there a nice way in MySQL to replicate the SQL Server function ROW_NUMBER()
?
For example:
SELECT
col1, col2,
ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow
FROM Table1
Then I could, for example, add a condition to limit intRow
to 1 to get a single row with the highest col3
for each (col1, col2)
pair.
Best Answer
There is no ranking functionality in MySQL. The closest you can get is to use a variable:
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.