ROW_NUMBER() in MySQL

Question

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.

Answer 1

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.

Answer 2

Also a bit late but today I had the same need so I did search on Google and finally a simple general approach found here in Pinal Dave's article http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/

I wanted to focus on Paul's original question (that was my problem as well) so I summarize my solution as a working example.

Beacuse we want to partition over two column I would create a SET variable during the iteration to identify if a new group was started.

SELECT col1, col2, col3 FROM (
  SELECT col1, col2, col3,
         @n := CASE WHEN @v = MAKE_SET(3, col1, col2)
                    THEN @n + 1 -- if we are in the same group
                    ELSE 1 -- next group starts so we reset the counter
                END AS row_number,
         @v := MAKE_SET(3, col1, col2) -- we store the current value for next iteration
    FROM Table1, (SELECT @n := 0, @v := NULL) r -- helper table for iteration with startup values
   ORDER BY col1, col2, col3 DESC -- because we want the row with maximum value
) x WHERE row_number = 1 -- and here we select exactly the wanted row from each group

The 3 means at the first parameter of MAKE_SET that I want both value in the SET (3=1|2). Of course if we do not have two or more columns constructing the groups we can eliminate the MAKE_SET operation. The construction is exactly the same. This is working for me as required. Many thanks to Pinal Dave for his clear demonstration.

Answer 3

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.)

Answer 4

query for row_number in mysql

set @row_number=0;
select (@row_number := @row_number +1) as num,id,name from sbs

Answer 5

set @i = 1;  
INSERT INTO ARG_VALUE_LOOKUP(ARG_VALUE_LOOKUP_ID,ARGUMENT_NAME,VALUE,DESCRIPTION,UPDATE_TIMESTAMP,UPDATE_USER,VER_NBR,OBJ_ID) 
select @i:= @i+1 as ARG_VALUE_LOOKUP_ID,ARGUMENT_NAME,VALUE,DESCRIPTION,CURRENT_TIMESTAMP,'admin',1,UUID() 
FROM TEMP_ARG_VALUE_LOOKUP 
order by ARGUMENT_NAME;

Answer 6

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!