By Paul


2009-12-12 23:58:44 8 Comments

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.

23 comments

@Caius Jard 2019-03-05 06:56:50

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)

@philipxy 2019-07-03 02:22:47

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

@Caius Jard 2019-07-03 04:47:02

Can you supply more detail phil?

@philipxy 2019-07-03 07:05:56

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.

@philipxy 2019-07-03 08:44:37

@Caius Jard 2019-07-03 08:51:38

I understand your concern

@Mohideen bin Mohammed 2019-05-14 06:15:26

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;

@Rishabh Pandey 2018-05-11 13:27:40

This could also be a solution:

SET @row_number = 0;

SELECT 
    (@row_number:[email protected]_number + 1) AS num, firstName, lastName
FROM
    employees

@Caius Jard 2019-03-05 07:07:25

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

@Quincy 2011-01-04 16:53:07

I would define a function:

delimiter $$
DROP FUNCTION IF EXISTS `getFakeId`$$
CREATE FUNCTION `getFakeId`() RETURNS int(11)
    DETERMINISTIC
begin
return if(@fakeId, @fakeId:[email protected]+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.

@Stephan Richter 2016-12-23 08:07:40

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

@jmpeace 2017-08-10 00:29:38

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

@Lukasz Szozda 2017-10-15 10:01:04

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

@Used_By_Already 2017-10-15 10:32:54

sigh... at last !

@Lukasz Szozda 2017-10-15 11:38:10

@Used_By_Already Yup, MySQL goes modern :)

@ceregala 2017-06-27 18:00:39

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

@Md. Kamruzzaman 2016-12-27 09:15:38

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

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

@Nickson Nyabote 2016-09-02 13:04:01

SELECT 
    col1, col2, 
    count(*) as intRow
FROM Table1
GROUP BY col1,col2
ORDER BY col3 desc

@Rick James 2016-04-27 04:51:15

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.

@user5528503 2015-11-05 09:20:44

query for row_number in mysql

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

@Diego 2016-08-02 21:25:23

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

@Diego 2016-08-03 13:21:08

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.

@abcdn 2015-08-19 20:48:49

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.

@jberryman 2017-04-26 18:26:48

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

@abcdn 2017-04-26 20:03:10

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.

@jberryman 2017-04-26 20:18:31

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

@abcdn 2017-04-26 20:21:05

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?

@philipxy 2017-11-15 22:16:39

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

@Serge Profafilecebook 2015-06-16 09:21:49

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:[email protected]_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:[email protected]_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
    

@user4605644 2015-02-25 12:25:45

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;

@Yannick Meeus 2015-02-25 12:31:50

Please try formatting any answers and give some additional context as to what you're trying to do. At the moment it's nothing more but poorly formatted text.

@Jeroen Mostert 2015-02-25 12:55:28

This doesn't appear to have any relationship to the original question. If you have a question of your own, please ask it separately.

@user3503199 2015-02-11 09:35:40

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:[email protected]+1, ...; However, the order of evaluation for expressions involving user variables is undefined.

Regards, Georgi.

@Tom Chiverton 2015-04-27 14:53:27

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" ?

@philipxy 2019-07-03 02:15:25

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

@Miklos Krivan 2014-11-15 12:31:31

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.

@pnomolos 2016-10-19 18:09:01

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

@Alankar 2014-05-10 00:45:46

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

@Peter Johnson 2010-08-12 17:26:13

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

@sholsinger 2011-08-14 19:54:49

The first := seems to be missing from @OMG Ponies answer. Thanks for posting this Peter Johnson.

@andig 2013-12-11 10:49:17

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

@Tom Chiverton 2015-04-27 14:55:17

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

@ExStackChanger 2015-09-17 09:51:14

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

@jberryman 2017-04-26 16:51:11

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

@Darós 2018-04-11 03:45:29

That's works here. Thanks

@Art 2013-03-21 20:08:48

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

@Álvaro González 2015-10-20 14:11:19

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

@snydergd 2013-02-21 16:21:25

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.

@OMG Ponies 2009-12-13 00:05:28

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.

@Paul 2009-12-13 00:07:44

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

@Paul 2009-12-13 00:46:08

Thanks...as I said above, this answer is equally accepted bobince's, but I can only tick one :-)

@Roland Bouman 2010-01-11 13:51:59

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

@OMG Ponies 2010-01-11 16:39:55

@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

@thomaspaulb 2010-11-08 03:50:25

@csonuryilmaz 2013-10-08 13:18:31

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

@Aaska Patel 2017-02-16 13:07:27

Thank you so much ..!!! Great !! It Worked for me

@jberryman 2017-04-26 16:33:29

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

@Mosty Mostacho 2012-03-11 04:09:35

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!

@Tushar 2014-07-07 08:44:01

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

@luckykrrish 2014-08-13 16:41:11

You are awesome Mosty, I'm looking exactly for this

@Utsav 2015-12-29 04:15:52

Just gave this answer using your logic for row_number. Thanks.

@alex 2017-08-18 21:57:41

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

@Almaz Vildanov 2018-05-06 08:28:44

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

@Zax 2018-08-10 08:22:36

@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!

@javadba 2019-04-21 01:46:54

I am linking/using this answer at stackoverflow.com/questions/55778739/…

@birch 2011-11-18 03:15:51

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.

@Stuart Watt 2015-09-22 14:16:18

Awesome. This actually does the partitioning. Very handy

@Kenneth Xu 2016-04-24 17:23:52

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 ```

@xmedeko 2017-03-07 19:54:11

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

@xmedeko 2017-03-07 19:57:36

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.

@Conan 2017-08-16 03:44:55

good job, very nice workaround..

@user2426679 2018-10-19 23:41:21

op's link is dead; archive of link here

@bobince 2009-12-13 00:14:14

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

@Paul 2009-12-13 00:16:39

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

@bobince 2009-12-13 00:18:54

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

@Paul 2009-12-13 00:21:38

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.

@bobince 2009-12-13 00:22:44

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.

@bobince 2009-12-13 00:24:58

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.

@Paul 2009-12-13 00:44:18

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.

@Bill Karwin 2009-12-13 01:15:10

@bobince: There's an easy solution to get the top N rows. See stackoverflow.com/questions/1442527/…

@Paul 2009-12-13 01:42:31

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

@bobince 2009-12-13 02:24:49

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

@newtover 2012-01-10 13:16:36

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.

@Jon Armstrong - Xgc 2012-11-19 00:47:00

@Paul - 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 ;

@wrschneider 2017-05-22 00:40:29

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)

@bobince 2017-05-22 22:02:22

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

@whyer 2019-04-30 16:39:29

@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

Related Questions

Sponsored Content

7 Answered Questions

24 Answered Questions

[SOLVED] How to reset AUTO_INCREMENT in MySQL?

45 Answered Questions

[SOLVED] How do I import an SQL file using the command line in MySQL?

36 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

25 Answered Questions

[SOLVED] Retrieving the last record in each group - MySQL

4 Answered Questions

[SOLVED] MySQL - Get row number on select

11 Answered Questions

[SOLVED] SQL Server: How to Join to first row

2 Answered Questions

[SOLVED] SQL ROW_NUMBER gives error

4 Answered Questions

[SOLVED] How do I get a SQL row_number equivalent for a Spark RDD?

2 Answered Questions

[SOLVED] SQL Adding row numbers

  • 2018-01-10 11:04:05
  • Mags
  • 53 View
  • 1 Score
  • 2 Answer
  • Tags:   sql sql-server

Sponsored Content