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.

Rubio

for a simple mysql row number function, check out datamakessense.com/mysql-rownum-row-number-function – Alga

MySql 8 now has ROW_NUMBER() and RANK(), see answer far below – Lancewood

@JimDavis Yes, that would be https://mcmap.net/q/98260/-row_number-in-mysql. Doc: dev.mysql.com/doc/refman/8.0/en/… – Cardona

Answer 1

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

Zindman

But what if there are two maximal values of col3 for a (col1, col2) pair? You'd end up with two rows. – Rubio

@Paul: yes! Just added a note about that in the answer a tic ago. You can usually easily drop unwanted extra rows in the application layer afterwards on some random basis, but if you have a lot of rows all with the same col3 it can be problematic. – Zindman

In t-sql I tend to need this as a sub-query as part of a much larger query, so post-processing isn't really an option. Also...what if you wanted the rows with the top n highest rows values of col3? With my t-sql example, you can add the constraint of intRow <= n, but this would be very hard with a self-join. – Rubio

If you took “with the single highest col3” literally you could make it return no rows instead of 2 in this case by using >= instead of >. But that's unlikely to be what you want! Another option in MySQL is to finish with GROUP BY col1, col2 without using an aggregate expression for col3; MySQL will pick a row at random. However this is invalid in ANSI SQL and generally considered really bad practice. – Zindman

For top N rows you have to add more joins or subqueries for each N, which soon gets unwieldy. Unfortunately LIMIT does not work in subqueries and there's no other arbitrary-selection-order or general windowsing function. – Zindman

Thanks, yes that makes sense. In the case of multiple maxima it certainly will have to be an arbitrary row, so the GROUP BY seems logical. The extra joins or subqueries sound a bit dubious though, especially if n is variable. The choice of preferred answer is a toss-up between this and OMG Ponies', as they both will replicate the functionality I need, but in a somewhat hard-to-read, slightly hacky way. – Rubio

@bobince: There's an easy solution to get the top N rows. See #1443027 – Hassiehassin

@Bill Karwin: That's a nice solution. Although in this case, the column we're sorting upon isn't necessarily unique so we may get more than n values. – Rubio

@Bill: nifty! What's the performance like on this sort of query, generally? Seeing heavy lifting in HAVING always makes me nervous. :-) – Zindman

bobince, the solution became rather popular here on SO, but I have a question. The solution is basically the same as if someone would try to find the largest id with the following query: SELECT t1.id FROM test t1 LEFT JOIN test t2 ON t1.id>t2.id WHERE t2.id IS NULL; Does not it require n*n/2 + n/2 IS NULL comparisons to find the single row? Do there happen any optimizations I do not see? I tried to ask the similar question to Bill in another thread but he seems to have ignored it. – Vanadium

@Rubio - To address the case where multiple rows exist that match the max per group and you wish to grab just one, you can always add the primary key in the ON clause logic to break the tie... 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, t1.pk) > (t0.col3, t0.pk) WHERE t1.col1 IS NULL ; – Flavius

This would be more readable as SELECT t0.col3 FROM table AS t0 WHERE NOT EXISTS (select 1 from table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3) – Mouthwash

@wrschneider: It would be more readable, but at the time this answer was written, likely much slower. Subquery support was a relative latecomer to MySQL and initially performed poorly. I would hope today both queries would be pretty optimal, but I can't say I've been keeping track of developments... – Zindman

@JonArmstrong-Xgc, btw if one had a multi-criteria sorting with different sorting order like ORDER BY col1 ASC, col2 ASC, pk DESC etc AND one of the sorting orders (either ASC or DESC had only numeric criterion like int or float), then one may simply add a minus sign before the numeric criterion of the opposite sorting order, e.g. (t1.col3, -t1.pk) > (t0.col3, -t0.pk), otherwise have to manually specify: t1.col3 > t0.col3 OR t1.col3 = t0.col3 AND STRCMP(t1.surname, t0.surname) < 0 – Physiotherapy

Answer 2

There is no ranking functionality in MySQL 5.7 or below. (This is supported in MySQL v8.0+, see @LukaszSzozda's answer)

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.

Pabulum

Hmm....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..? – Rubio

Assigning to and reading from user-defined variables in the same statement is not reliable. this is documented here: dev.mysql.com/doc/refman/5.0/en/user-variables.html: "As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement." – Bireme

@Roland: I've only tested on small datasets, haven't had any issue. Too bad MySQL has yet to address the functionality - the request has been in since 2008 – Pabulum

A nice example ishere: artfulsoftware.com/infotree/queries.php?&bw=1440#104 – Spill

According to my experience if you use INNER JOINs in your query, use ",(SELECT @rownum := 0) r" statement after INNER JOINs. – Squabble

This seems to be undefined behavior as Roland notes. e.g. this gives totally incorrect results for a table I tried: SELECT @row_num:=@row_num+1 AS row_number, t.id FROM (SELECT * FROM table1 WHERE col = 264 ORDER BY id) t, (SELECT @row_num:=0) var; – Interregnum

Is this work on mysql? I got syntax error when I run it ... – Armillary

For me this stopped working in MySQL 8.0.22. – Bluenose

For MySQL 8+, use the built-in row_number() solution instead of this one: https://mcmap.net/q/98260/-row_number-in-mysql/… – Personification

Answer 3

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

Connolly

if columns are VARCHAR or CHAR, how can you handle that with this structure? – Costanza

@Costanza the operators <, >, <=, >= handle CHAR and VARCHAR data types on alphabetic order; I expect, is exactly what you are looking for. – Jodeejodhpur

can I add a condition where row_number <= 2? And How? – Heaviness

@AlmazVildanov you should be able to use this query simply as a subquery fo filter out row_numbers <= 2 And huge thanks for this answer Mosty, it's perfect! – Rumrunner

Answer 4

SELECT 
    @i:=@i+1 AS iterator, 
    t.*
FROM 
    tablename AS t,
    (SELECT @i:=0) AS foo

Gerent

I guess (SELECT @i:=0) AS foo should be the first table in the FROM statement, especially if other tables use sub-selects – Walterwalters

Why do you even need the '.. as foo' ? – Holton

@TomChiverton If it's missing, you get: "Error Code: 1248. Every derived table must have its own alias" – Responsum

The rank assignment here is completely undefined and this doesn't even answer the question – Interregnum

Answer 5

From MySQL 8.0.0 and above you could natively use windowed functions.

1.4 What Is New in MySQL 8.0:

Window functions.

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.

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

Vane

Answer 6

Check out this Article, it shows how to mimic SQL ROW_NUMBER() with a partition by in MySQL. I ran into this very same scenario in a WordPress Implementation. I needed ROW_NUMBER() and it wasn't there.

http://www.explodybits.com/2011/11/mysql-row-number/

The example in the article is using a single partition by field. To partition by additional fields you could do something like this:

  SELECT  @row_num := IF(@prev_value=concat_ws('',t.col1,t.col2),@row_num+1,1) AS RowNumber
         ,t.col1 
         ,t.col2
         ,t.Col3
         ,t.col4
         ,@prev_value := concat_ws('',t.col1,t.col2)
    FROM table1 t,
         (SELECT @row_num := 1) x,
         (SELECT @prev_value := '') y
   ORDER BY t.col1,t.col2,t.col3,t.col4 

Using concat_ws handles null's. I tested this against 3 fields using an int, date, and varchar. Hope this helps. Check out the article as it breaks this query down and explains it.

Claresta

Awesome. This actually does the partitioning. Very handy – Epicontinental

Comparing to self join, this is much more efficient, but there is an issue with the logic, order must occur before computing row_num, concat is also not necessary. ``` SELECT @row_num := IF(@prev_col1=t.col1 AND @prev_col2=t.col2), @row_num+1, 1) AS RowNumber ,t.col1 ,t.col2 ,t.col3 ,t.col4 ,@prev_col1 := t.col1 ,@prev_col2 := t.col2 FROM (SELECT * FROM table1 ORDER BY col1, col2, col3) t, (SELECT @row_num := 1, @prev_col1 := '', @prev_col2 := '') var ``` – Drillmaster

If you need tu put this into a subquery, then add limit 18446744073709551615 to force order by clause. – Lemkul

concat_ws with empty string '' is dangerous: concat_ws('',12,3) = concat_ws('',1,23). Better to use some separator '_' or use @Kenneth Xu solution. – Lemkul

op's link is dead; archive of link here – Shockey

Answer 7

I would also vote for Mosty Mostacho's solution with minor modification to his query code:

SELECT a.i, a.j, (
    SELECT count(*) from test b where a.j >= b.j AND a.i = b.i
) AS row_number FROM test a

Which will give the same 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 |
+------+------+------------+

for the table:

+------+------+
|    i |    j |
+------+------+
|    1 |   11 |
|    1 |   12 |
|    1 |   13 |
|    2 |   21 |
|    2 |   22 |
|    2 |   23 |
|    3 |   31 |
|    3 |   32 |
|    3 |   33 |
|    4 |   14 |
+------+------+

With the only difference that the query doesn't use JOIN and GROUP BY, relying on nested select instead.

Programme

Is this supposed to be better? They both seem likely to be quadratic, but I'm not sure how to interprate the EXPLAIN output – Interregnum

In fact, nested selects are known to be not very well optimized in MySQL, so this anwser is just for demonstration of a querying technique. The variable-based examples above work better for most practical cases, I suppose. – Programme

I'm not convinced any of the variable based answers are actually using defined behavior... – Interregnum

I am sorry, I am not sure I got what you meant by "defined behavior". Do you mean it doesn't work for you, or you are just concerned that it is not documented? – Programme

"Undefined behaviour" means that it is not documented to work and/or documented to not be guaranteed to work. See documentation quotes & links in comments on this page. It might return what one (unsoundly) wants/guesses/hypothesizes/fantasizes. For certain versions of the implementation certain query expressions using CASE incrementing & using variables has been shown to work by programmers at Percona by looking at the code. That could change with any release. – Francescafrancesco

Answer 8

I would define a function:

delimiter $$
DROP FUNCTION IF EXISTS `getFakeId`$$
CREATE FUNCTION `getFakeId`() RETURNS int(11)
    DETERMINISTIC
begin
return if(@fakeId, @fakeId:=@fakeId+1, @fakeId:=1);
end$$

then I could do:

select getFakeId() as id, t.* from table t, (select @fakeId:=0) as t2;

Now you don't have a subquery, which you can't have in views.

Antidromic

Works with one limitation: if you execute the query several times, you will get ever-increasing fakeIds for the same result set – Bogusz

you could send set @fakeId =0; each time you want to run the query, not optimal but works – Weinberger

A really odd issue happens if you remove DETERMINISTIC. Then the fakeId is incorrect when using order by. Why is this? – Cenozoic

Answer 9

query for row_number in mysql

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

Zooplasty

This can be used on UPDATE queries? I am trying but I get a "data truncated for column..." error. – Mercury

If anyone is interested on using it on UPDATE, it must be used as a sub-query in order to work. UPDATE <table> SET <field> = (SELECT \@row_number := \@row_number +1) ORDER BY <your order column>; The order column determines the value ordering of the rows. – Mercury

Answer 10

There is no funtion like rownum, row_num() in MySQL but the way around is like below:

select 
      @s:=@s+1 serial_no, 
      tbl.* 
from my_table tbl, (select @s:=0) as s;

Lamarckism

Answer 11

Important: Please consider upgrading to MySQL 8+ and use the defined and documented ROW_NUMBER() function, and ditch old hacks tied to a feature limited ancient version of MySQL

Now here's one of those hacks:

The answers here that use in-query variables mostly/all seem to ignore the fact that the documentation says (paraphrase):

Don't rely on items in the SELECT list being evaluated in order from top to bottom. Don't assign variables in one SELECT item and use them in another one

As such, there's a risk they will churn out the wrong answer, because they typically do a

select
  (row number variable that uses partition variable),
  (assign partition variable)

If these are ever evaluated bottom up, the row number will stop working (no partitions)

So we need to use something with a guaranteed order of execution. Enter CASE WHEN:

SELECT
  t.*, 
  @r := CASE 
    WHEN col = @prevcol THEN @r + 1 
    WHEN (@prevcol := col) = null THEN null
    ELSE 1 END AS rn
FROM
  t, 
  (SELECT @r := 0, @prevcol := null) x
ORDER BY col

As outline ld, order of assignment of prevcol is important - prevcol has to be compared to the current row's value before we assign it a value from the current row (otherwise it would be the current rows col value, not the previous row's col value).

Here's how this fits together:

• The first WHEN is evaluated. If this row's col is the same as the previous row's col then @r is incremented and returned from the CASE. This return led values is stored in @r. It's a feature of MySQL that assignment returns the new value of what is assigned into @r into the result rows.

• For the first row on the result set, @prevcol is null (it is initialised to null in the subquery) so this predicate is false. This first predicate also returns false every time col changes (current row is different to previous row). This causes the second WHEN to be evaluated.

• The second WHEN predicate is always false, and it exists purely to assign a new value to @prevcol. Because this row's col is different to the previous row's col (we know this because if it were the same, the first WHEN would have been used), we have to assign the new value to keep it for testing next time. Because the assignment is made and then the result of the assignment is compared with null, and anything equated with null is false, this predicate is always false. But at least evaluating it did its job of keeping the value of col from this row, so it can be evaluated against the next row's col value

• Because the second WHEN is false, it means in situations where the column we are partitioning by (col) has changed, it is the ELSE that gives a new value for @r, restarting the numbering from 1

We this get to a situation where this:

SELECT
  t.*, 
  ROW_NUMBER() OVER(PARTITION BY pcol1, pcol2, ... pcolX ORDER BY ocol1, ocol2, ... ocolX) rn
FROM
  t

Has the general form:

SELECT
  t.*, 
  @r := CASE 
    WHEN col1 = @pcol1 AND col2 = @pcol2 AND ... AND colX = @pcolX THEN @r + 1 
    WHEN (@pcol1 := pcol1) = null OR (@pcol2 := col2) = null OR ... OR (@pcolX := colX) = null THEN null
    ELSE 1 
  END AS rn
FROM
  t, 
  (SELECT @r := 0, @pcol1 := null, @pcol2 := null, ..., @pcolX := null) x
ORDER BY pcol1, pcol2, ..., pcolX, ocol1, ocol2, ..., ocolX

Footnotes:

• The p in pcol means "partition", the o in ocol means "order" - in the general form I dropped the "prev" from the variable name to reduce visual clutter

• The brackets around (@pcolX := colX) = null are important. Without them you'll assign null to @pcolX and things stop working

• It's a compromise that the result set has to be ordered by the partition columns too, for the previous column compare to work out. You can't thus have your rownumber ordered according to one column but your result set ordered to another You might be able to resolve this with subqueries but I believe the docs also state that subquery ordering may be ignored unless LIMIT is used and this could impact performance

• I haven't delved into it beyond testing that the method works, but if there is a risk that the predicates in the second WHEN will be optimised away (anything compared to null is null/false so why bother running the assignment) and not executed, it also stops. This doesn't seem to happen in my experience but I'll gladly accept comments and propose solution if it could reasonably occur

• It may be wise to cast the nulls that create @pcolX to the actual types of your columns, in the subquery that creates the @pcolX variables, viz: select @pcol1 := CAST(null as INT), @pcol2 := CAST(null as DATE)

Targett

There is no justification for this. Just like the other answers that assign to & read from the same variable. – Francescafrancesco

Can you supply more detail phil? – Targett

See my other comments on this page. Googling 'site:stackoverflow.com "philipxy" mysql variable (set OR assign OR assignment OR write) read': An answer by me & a bug report linked in a comment by me at this question where the accepted answer quotes the manual yet immediately in claims it's OK to do something in contradiction to it. Read the manual re variables & re assignment. – Francescafrancesco

MySQL Server Blog – Francescafrancesco

I understand your concern – Targett

I wonder when WHEN (@prevcol := col) = null THEN null will be executed. Did you mean null is null? = null is always unknown, which means false. – Moses

@Moses You need to read the whole answer. I explain in detail why this second when clause is structured so that it is guaranteed to always be false. If you don't want to read the whole answer, Ctrl-F for The second WHEN predicate is always false and read the bullet point that starts with this sentence – Targett

Answer 12

The solution I found to work the best was using a subquery like this:

SELECT 
    col1, col2, 
    (
        SELECT COUNT(*) 
        FROM Table1
        WHERE col1 = t1.col1
        AND col2 = t1.col2
        AND col3 > t1.col3
    ) AS intRow
FROM Table1 t1

The PARTITION BY columns just get compared with '=' and separated by AND. The ORDER BY columns would be compared with '<' or '>', and separated by OR.

I've found this to be very flexible, even if it is a little bit costly.

Archetype

Answer 13

The rownumber functionality can't be mimicked. You might get the results you expect, but you'll most likely get disappointed at some stage. Here's what mysql documentation says:

For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second: SELECT @a, @a:=@a+1, ...; However, the order of evaluation for expressions involving user variables is undefined.

Regards, Georgi.

Hakeem

I don't follow. How is "@i := @i + 1 as position" not a direct replacement for "ROW_NUMBER() over (order by sum(score) desc) as position" ? – Holton

@TomChiverton Because its behaviour is not defined, as the manual says right there. – Francescafrancesco

Answer 14

MariaDB 10.2 is implementing "Window Functions", including RANK(), ROW_NUMBER() and several other things:

https://mariadb.com/kb/en/mariadb/window-functions/

Based on a talk at Percona Live this month, they are reasonably well optimized.

The syntax is identical to the code in the Question.

Single

Answer 15

MySQL has supported the ROW_NUMBER() since version 8.0+.

If you use MySQL 8.0 or later, check it out ROW_NUMBER() function. Otherwise, you have emulate ROW_NUMBER() function.

The row_number() is a ranking function that returns a sequential number of a row, starting from 1 for the first row.

for older version,

SELECT t.*, 
       @rowid := @rowid + 1 AS ROWID
  FROM TABLE t, 
       (SELECT @rowid := 0) dummy;

Histogenesis

Answer 16

This allows the same functionality that ROW_NUMBER() AND PARTITION BY provides to be achieved in MySQL

SELECT  @row_num := IF(@prev_value=GENDER,@row_num+1,1) AS RowNumber
       FirstName, 
       Age,
       Gender,
       @prev_value := GENDER
  FROM Person,
      (SELECT @row_num := 1) x,
      (SELECT @prev_value := '') y
  ORDER BY Gender, Age DESC

Letta

Answer 17

I don't see any simple answer covering the "PARTITION BY" part so here's mine :

SELECT
    *
FROM (
    select
        CASE WHEN @partitionBy_1 = l THEN @row_number:=@row_number+1 ELSE @row_number:=1 END AS i
        , @partitionBy_1:=l AS p
        , t.*
    from (
        select @row_number:=0,@partitionBy_1:=null
    ) as x
    cross join (
        select 1 as n, 'a' as l
        union all
        select 1 as n, 'b' as l    
        union all
        select 2 as n, 'b' as l    
        union all
        select 2 as n, 'a' as l
        union all
        select 3 as n, 'a' as l    
        union all    
        select 3 as n, 'b' as l    
    ) as t
    ORDER BY l, n
) AS X
where i > 1

• The ORDER BY clause must reflect your ROW_NUMBER need. Thus there's already a clear limitation: you can't have several ROW_NUMBER "emulation" of this form at the same time.
• The order of the "computed column" matters. If you have mysql compute those column in another order, it might not work.

• In this simple example I only put one but you can have several "PARTITION BY" parts

      CASE WHEN @partitionBy_1 = part1 AND @partitionBy_2 = part2 [...] THEN @row_number:=@row_number+1 ELSE @row_number:=1 END AS i
      , @partitionBy_1:=part1 AS P1
      , @partitionBy_2:=part2 AS P2
      [...] 
  FROM (
      SELECT @row_number:=0,@partitionBy_1:=null,@partitionBy_2:=null[...]
  ) as x
  

Solus

Answer 18

This could also be a solution:

SET @row_number = 0;

SELECT 
    (@row_number:=@row_number + 1) AS num, firstName, lastName
FROM
    employees

Stentorian

It doesn't do any partitioning though, and it isn't significantly different to a higher cited answer – Targett

Answer 19

Solutions with cross join and comma won't work if your query has GROUP BY statement. For such cases you can use subselect:

SELECT (@row_number := @row_number + 1) AS rowNumber, res.*
FROM
(
  SELECT SUM(r.amount) 
  FROM Results r 
  WHERE username = 1 
  GROUP BY r.amount
) res
CROSS JOIN (SELECT @row_number := 0) AS dummy

Severn

Answer 20

I think you can use DENSE_RANK() function here. Example:

select `score`, DENSE_RANK() OVER( ORDER BY score desc ) as `rank` from Scores;

https://www.mysqltutorial.org/mysql-window-functions/mysql-dense_rank-function/

Indention

Answer 21

A bit late but may also help to someone who looks for answers...

Between rows/row_number example - recursive query that may be used in any SQL:

WITH data(row_num, some_val) AS 
(
 SELECT 1 row_num, 1 some_val FROM any_table --dual in Oracle
  UNION ALL
 SELECT row_num+1, some_val+row_num FROM data WHERE row_num < 20 -- any number
)
SELECT * FROM data
 WHERE row_num BETWEEN 5 AND 10
/

ROW_NUM    SOME_VAL
-------------------
5           11
6           16
7           22
8           29
9           37
10          46

Separation

Sorry but as far as I know MySQL does not support common table expressions. – Discrepancy

it does now ... @ÁlvaroGonzález MySQL 8 only supports CTE and window functions, so this answer does not really make sense to use in older MySQL versions.. – Retrace

Answer 22

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.

Harlandharle

Note that ORDER BY in a subquery could be ignored (see mariadb.com/kb/en/mariadb/…). The suggested solution to that is to add LIMIT 18446744073709551615 to the subquery, which forces a sort. However this could cause performance issues and isn't valid for really freaking huge tables :) – Marriott

Answer 23

This is not the most robust solution - but if you're just looking to create a partitioned rank on a field with only a few different values, it may not be unwieldily to use some case when logic with as many variables as you require.

Something like this has worked for me in the past:

SELECT t.*, 
   CASE WHEN <partition_field> = @rownum1 := @rownum1 + 1 
     WHEN <partition_field> = @rownum2 := @rownum2 + 1 
     ...
     END AS rank
FROM YOUR_TABLE t, 
   (SELECT @rownum1 := 0) r1, (SELECT @rownum2 := 0) r2
ORDER BY <rank_order_by_field>
;

Hope that makes sense / helps!

Billie

Answer 24

Still supporting MySQL 5.7.38 in 2023, and needing ROW_NUMBER() I ended up doing something like this:

drop temporary table t1

create temporary table t1 (
    USER_ID VARCHAR(50),
    PRIORITY INT
)

insert into t1 (USER_ID, PRIORITY ) 
values 
('qqq',300),
('qqq',572),
('qqq',574),
('qqq',630),
('qqq',640),
('qqq',650),
('yyy',300),
('yyy',574),
('yyy',574),
('yyy',630),
('yyy',640),
('yyy',650)

    
 SELECT *,
    @row_number := IF(@prev_userid = USER_ID, @row_number + 1, 1) AS ROWNUM,
    @prev_userid := USER_ID
FROM t1
CROSS JOIN (SELECT @row_number := 0, @prev_userid := '') AS vars
ORDER BY USER_ID, PRIORITY

Results:

|USER_ID|PRIORITY|@row_number := 0|@prev_userid := ''|ROWNUM|@prev_userid := USER_ID|
|-------|--------|----------------|------------------|------|-----------------------|
|qqq    |300     |0               |                  |1     |qqq                    |
|qqq    |572     |0               |                  |2     |qqq                    |
|qqq    |574     |0               |                  |3     |qqq                    |
|qqq    |630     |0               |                  |4     |qqq                    |
|qqq    |640     |0               |                  |5     |qqq                    |
|qqq    |650     |0               |                  |6     |qqq                    |
|yyy    |300     |0               |                  |1     |yyy                    |
|yyy    |574     |0               |                  |2     |yyy                    |
|yyy    |574     |0               |                  |3     |yyy                    |
|yyy    |630     |0               |                  |4     |yyy                    |
|yyy    |640     |0               |                  |5     |yyy                    |
|yyy    |650     |0               |                  |6     |yyy                    |

Shantelleshantha

Answer 25

This Work perfectly for me to create RowNumber when we have more than one column. In this case two column.

SELECT @row_num := IF(@prev_value= concat(`Fk_Business_Unit_Code`,`NetIQ_Job_Code`), @row_num+1, 1) AS RowNumber, 
    `Fk_Business_Unit_Code`,   
    `NetIQ_Job_Code`,  
    `Supervisor_Name`,  
    @prev_value := concat(`Fk_Business_Unit_Code`,`NetIQ_Job_Code`)  
FROM (SELECT DISTINCT `Fk_Business_Unit_Code`,`NetIQ_Job_Code`,`Supervisor_Name`         
      FROM Employee    
      ORDER BY `Fk_Business_Unit_Code`, `NetIQ_Job_Code`, `Supervisor_Name` DESC) z,  
(SELECT @row_num := 1) x,  
(SELECT @prev_value := '') y  
ORDER BY `Fk_Business_Unit_Code`, `NetIQ_Job_Code`,`Supervisor_Name` DESC

Udele

Answer 26

for the partioning over anothe column one way is that described by @abcdn. However, it has a low performance. I propose use this code which does not require joining a table with itself: Considee the same table.

you can get paritioning like this:

set @row_num := 0;
set @j:= 0;

select IF(j= @j, @row_num := @row_num + 1, @row_num := 1) as row_num,
       i, @j:= j as j
from tbl fh
order by j, i;

the reult would be like this :

The advantage is we do not need to join table with itself

Tobacconist