ROW_NUMBER() in MySQL

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.

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.col3FROM table AS t0LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3WHERE 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.)

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 aJOIN test b ON a.i = b.i AND a.j >= b.jGROUP BY a.i, a.j

Hope that helps!