CODEWITHSUNDEEP
mysqlsqlwindow-functionsgaps-and-islands
o_yeah
o_yeah

Reputation: 788

SQL partition by with original order

Here's the original MySQL table:

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

When I use select Id, Num, row_number() over(partition by Num) from t, MySQL automatically disrupts the order of the Num column. However, I want to keep Num column order unchanged. Specifically, the ideal output should be like:

+----+-----+-----+
| Id | Num | row |
+----+-----+-----+
| 1  |  1  |  1  |
| 2  |  1  |  2  |
| 3  |  1  |  3  |
| 4  |  2  |  1  |
| 5  |  1  |  1  |
| 6  |  2  |  1  |
| 7  |  2  |  2  |
+----+-----+-----+

How to write this MySQL query?

Upvotes: 2

Views: 2280

Answers (3)

Somy
Somy

Reputation: 1624

Same idea as the solution proposed by Schwern. Just another style of syntax in MySQL which I find very simplistic and easy to use.

Select
    id
  , num
  , value
from
(select
      T.id,
      T.num,
      if( @lastnum = T.num, @Value := @Value + 1,@Value := 1) as Value,
      @lastnum := T.num as num2
   from
      mytable T,
      ( select @lastnum := 0,
               @Value := 1 ) SQLVars
   order by
      T.id) T;

DB fiddle link - https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e04692841d091ccd54ee3435a409c67a

Upvotes: 0

Schwern
Schwern

Reputation: 164669

You can do this by writing your own row_number to have greater control over its partitioning.

set @prev_num = null;
set @row_number = 0;

select
  id,
  -- Reset row_number to 1 whenever num changes, else increment it.
  @row_number := case
    when @prev_num = num then 
      @row_number + 1
    else
      1
    end as `row_number`,
  -- Emulate lag(). This must come after the row_number.
  @prev_num := num as num
from foo
order by id;

Upvotes: 1

GMB
GMB

Reputation: 222402

This is a gaps-and-islands problem. I would recommend using the difference between row numbers to identify the groups.

If id is always incrementing without gaps:

select id, num, 
    row_number() over(partition by num, id - rn order by id) rn
from (
    select t.*, row_number() over(partition by num order by id) rn
    from mytable t
) t
order by id

Otherwise, we can generate our own incrementing id with another row_number():

select id, num, 
    row_number() over(partition by num, rn1 - rn2 order by id) rn
from (
    select t.*, 
        row_number() over(order by id) rn1,
        row_number() over(partition by num order by id) rn2
    from mytable t
) t
order by id

Demo on DB Fiddle - for your sample data, both queries yield:

id | num | rn
-: | --: | -:
 1 |   1 |  1
 2 |   1 |  2
 3 |   1 |  3
 4 |   2 |  1
 5 |   1 |  1
 6 |   2 |  1
 7 |   2 |  2

Upvotes: 3

Related Questions