How do I set the record's position relative to a group in SQL?

Question

I have a series of records that can be grouped by a group_id

Example records:

╭───╥──────────┬───────────────────╮
│id ║ group_id │ position_in_group │
╞═══╬══════════╪═══════════════════╡
│ 1 ║    2     │        null       │
│ 2 ║    1     │        null       │
│ 3 ║    1     │        null       │
│ 4 ║    1     │        null       │
│ 5 ║    2     │        null       │
│ 6 ║    2     │        null       │
│ 7 ║    3     │        null       │
│ 8 ║    3     │        null       │
│ 9 ║    3     │        null       │
└───╨──────────┴───────────────────┘

I want to set the position_in_group for each record. It is the position of the record inside the group if I GROUP BY group_id.

For example: In the group with id 1, the record with id=2 is the first, so its position_in_group would be 1.

The final table would be:

╭───╥──────────┬───────────────────╮
│id ║ group_id │ position_in_group │
╞═══╬══════════╪═══════════════════╡
│ 1 ║    2     │        1          │
│ 2 ║    1     │        1          │
│ 3 ║    1     │        2          │
│ 4 ║    1     │        3          │
│ 5 ║    2     │        2          │
│ 6 ║    2     │        3          │
│ 7 ║    3     │        1          │
│ 8 ║    3     │        2          │
│ 9 ║    3     │        3          │
└───╨──────────┴───────────────────┘

Is there any way I can do this in a SQL query?

This would be done using `row_number() over (partition by group_id order by id)` in oracle and sqlserver. Check here for mysql equivalent options: http://stackoverflow.com/q/1895110/6205293 — msheikh25, Jun 27 '16 at 21:23
yes, a query with variables ( @'s) ... someone bang one out. — Drew, Jun 27 '16 at 21:35

score 1 · Answer 1 · answered Jun 27 '16 at 21:36

One method is to use variables. A bit challenging in MySQL, but it can look like this:

set @g := -1;
set @rn := 0;

update t
    set position_in_group = (@rn := if(@g = group_id, @rn + 1,
                                       if(@g := group_id, 1, 1)
                                      )
                            )
    order by group_id, id;

Note: You need to initialize the variables separately from the update statement, because MySQL does not support joins and order by in the same update statement.

score 0 · Answer 2 · answered Jun 27 '16 at 22:57

There's no need to store this data...

SELECT id
     , group_id
     , rank
  FROM 
     ( SELECT id
            , group_id
            , CASE WHEN group_id = @prev THEN @i:=@i+1 ELSE @i:=1 END rank 
            , @prev:=group_id prev 
         FROM my_table x
            , (SELECT @i:=1,@prev:=null) vars 
        ORDER 
           BY group_id
            , id
     ) a
 ORDER
    BY id;

...but if you really want to...

UPDATE my_table a
  JOIN 
     ( SELECT id
            , group_id
            , CASE WHEN group_id = @prev THEN @i:=@i+1 ELSE @i:=1 END rank 
            , @prev:=group_id prev 
         FROM my_table x
            , (SELECT @i:=1,@prev:=null) vars 
        ORDER 
           BY group_id
            , id
     ) b
    ON b.id = a.id
   SET a.rank = b.rank;

score 0 · Answer 3 · edited May 23 '17 at 12:33

0

this solution was implemented by another user abcdn see https://stackoverflow.com/a/32105418/3762855

SELECT a.id, a.group_id, (
    SELECT count(*) from groups b where a.id >= b.id AND a.group_id = b.group_id
) AS row_number FROM groups a;

edited May 23 '17 at 12:33

Community

1
1

answered Jun 27 '16 at 23:19

Ciro Corvino

2,038
5
20
33

score 0 · Accepted Answer · answered Jun 28 '16 at 14:00

I found out a way to do this with pure SQL and without variables with a self JOIN:

UPDATE my_table
JOIN (
  SELECT c.aid AS id, COUNT(*)-1 AS position_in_group
  FROM (
    SELECT a.id AS aid, b.id AS bid, a.group_id 
    FROM my_table AS a
    JOIN my_table AS b ON (a.group_id=b.group_id AND a.id >= b.id)
  ) AS c
  GROUP BY c.aid
) AS d ON my_table.id = d.id
SET my_table.position_in_group = d.position_in_group;

I self join the table with itself to create matching pairs (records in the same group) without duplicate pairs. Then I just count those pairs grouping by the id of the left record.

How do I set the record's position relative to a group in SQL?

4 Answers4