Row number per group in mysql

Question

I want to produce query result base on this scenario that can create row number according to crew_id and type.

id   crew_id   amount    type
 1      4       1000      AUB
 2      4       1500      AUB
 3      5       8000      CA
 4      4       1000      CA
 5      5       1000      AUB
 6      6       3000      AUB
 7      4       2000      CA
 8      6       3500      AUB
 9      4       5000      AUB
 10     5       9000      CA
 11     5       1000      CA

OUTPUT must be the ff:

id    crew_id   amount   type    row_number
 1      4       1000      AUB        1    
 2      4       1500      AUB        2 
 9      4       5000      AUB        3
 4      4       1000      CA         1
 7      4       2000      CA         2
 5      5       1000      AUB        1
 3      5       8000      CA         1
 10     5       9000      CA         2
 11     5       1000      CA         3
 6      6       3000      AUB        1
 6      6       3000      AUB        2

I want a single select statement only in this output

All the answers break if you have more than table, force-subselect them: `SELECT ... CASE ... FROM (SELECT the real thing FROM t1 INNER JOIN t2) x` — chx, Sep 08 '19 at 08:32

Janty · Answer 1 · 2013-07-30T06:36:45.990

47

Please go through my fiddle

This One Last Tried

    SELECT    id,
              crew_id,
              amount,
              type,
             ( 
                CASE type 
                WHEN @curType 
                THEN @curRow := @curRow + 1 
                ELSE @curRow := 1 AND @curType := type END
              ) + 1 AS rank
    FROM      Table1 p,
              (SELECT @curRow := 0, @curType := '') r
   ORDER BY  crew_id,type asc;

edited Jul 30 '13 at 06:36

answered Jul 30 '13 at 06:23

Janty

1,708
2
15
29

Is possible to include crew_id? can you rank it according to crew_id and AUB? – user1852837 Jul 30 '13 at 07:07
There is a slight issue with this. If the type starts with a number the rank starts at 2 instead of 1. Any ideas how to fix this? – SystemParadox May 23 '16 at 12:38
1

@SystemParadox take the `+ 1` out from `) + 1 AS rank` – scrowler Sep 06 '16 at 22:52
What is the `(SELECT @curRow := 0, @curType := '') r` part for? – Som Dec 15 '16 at 13:08
Som, the syntax `tblTable1, tblTable2` results in a `CROSS JOIN` being performed on the two tables. This is where each row from `tblTable1` is reproduced with *each* row from `tblTable2` added on (read up on `CROSS JOIN`'s for more). In this case the equivalent of `tblTable2` has just one row, which results in `@curRow` and `@curType` being added as fields to `tblTable1`'s equivalent (`Table1`), with the field values always being `0` and `''`. This part also initialises the *variables* `@curRow` and `@curType` to `0` and `''`, respectively. – toonice Apr 26 '17 at 15:56

score 24 · Accepted Answer · answered Jun 08 '16 at 17:41

The question is quite old. But I would like to post it in case someone will have a same problem.

First of all, described answers do not work correct. For example, for

id   crew_id   amount    type
1      4       1000      AUB
2      4       1500      AUB
5      5       1000      AUB
6      6       3000      AUB
8      6       3500      AUB
9      4       5000      AUB

(I just removed rows with type 'CA') the result table will be

id   crew_id   amount    rank   type
1      4       1000      1      AUB
2      4       1500      2      AUB
9      4       5000      3      AUB
5      5       1000      4      AUB
6      6       3000      5      AUB
8      6       3500      6      AUB

So in fact it doesn't use both crew_id and type, it just uses type.

Here is how I solved this problem (probably there is a more elegant way to do it than use two nested 'CASE's, but you get the idea):

SELECT id,
    amount,
    CASE crew_id 
        WHEN @curCrewId THEN
            CASE type 
                WHEN @curType THEN @curRow := @curRow + 1 
                ELSE @curRow := 1
            END
        ELSE @curRow :=1
    END AS rank,
    @curCrewId := crew_id AS crew_id,
    @curType := type AS type
FROM Table1 p
JOIN (SELECT @curRow := 0, @curCrewId := 0, @curType := '') r
ORDER BY crew_id, type

The main idea remain. I just added a variable @curCrewId. If someone need to use 3 variables for grouping, so just use 3 variables and 3 nested 'CASE's. :)

Thank you for this simple answer ! Just an improvement : there is no need for JOIN clause as the variables are initialized in SELECT clause. — BenL, Apr 06 '17 at 17:47
@BenL It doesn't work without JOIN. Unfortunately I can't explain it correctly, but you can just try it:) — Rustam Guliev, Apr 08 '17 at 08:14
This doesn't work if the rows in the table are not ordered by crew_id & type. To fix this I added a sub query: `FROM (SELECT * FROM Table1 ORDER BY crew_id, type) p` — Adriaan Koster, Oct 16 '18 at 11:16
Just to add up to this answer, if you use a slightly modified syntax for the `CASE` statement you can compare all your grouping columns on one `CASE` without needing nesting, like this: `CASE WHEN @curCrewId = crew_id AND @curType = type THEN @curRow := @curRow + 1 ELSE @curRow := 1 END` — Óscar Gómez Alcañiz, Sep 03 '20 at 14:38

score 14 · Answer 3 · answered Apr 06 '21 at 06:37

14

With MySQL 8.0 you can use the window function ROW_NUMBER:

SELECT *, ROW_NUMBER() OVER(PARTITION BY crew_id) AS row_number
FROM MyTable

answered Apr 06 '21 at 06:37

Oldrich Dlouhy

597
6
6

This is just perfect for what I was looking for. I added an order by within the PARTITION clause and it was perfect to number rows within groups. – Eerko Oct 14 '22 at 11:11
^^This is the answer – waltmagic Nov 18 '22 at 23:31

score 4 · Answer 4 · answered May 23 '16 at 13:27

@Janty's answer does not work when the type starts with a number (the rank starts at 2 instead of 1).

Use the following instead:

SELECT id,
     crew_id,
     amount,
     CASE type 
         WHEN @curType THEN @curRow := @curRow + 1 
         ELSE @curRow := 1
     END AS rank,
     @curType := type AS type
FROM Table1 p
JOIN (SELECT @curRow := 0, @curType := '') r
ORDER BY crew_id, type

score 4 · Answer 5 · answered Apr 06 '17 at 17:02

4

In addition to the answer of @Janty here is a solution if you want to UDATE your table with the rownumber:

UPDATE myTable mt,(SELECT @curRow := 0, @curType := '') r SET type=
    ( 
        CASE type 
            WHEN @curType
        THEN @curRow := @curRow + 1 
        ELSE @curRow := 1 AND @curType := type END
      )
;

As janty too crewId is not within. Use a second "case" for that as mentioned in the other answers.

answered Apr 06 '17 at 17:02

phil

1,289
1
15
24

2

how to add order by clause – ASD Dec 13 '17 at 05:28

score 0 · Answer 6 · answered Jul 30 '13 at 07:17

SELECT id, crew_id, amount, type,
       ( 
        CASE type 
          WHEN @curType 
          THEN @curRow := @curRow + 1 
          ELSE @curRow := 1 AND @curType := type  END
      ) + 1 AS rank
 FROM      Table1 p,
      (SELECT @curRow := 0, @curType := '') r
   ORDER BY  crew_id, type asc;

Abbas Hosseini · Answer 7 · 2016-07-28T18:21:42.383

Here is my answer using only one case that creates row number according to both columns:

SELECT id, crew_id, amount, type,
   (CASE CONCAT(crew_id, type) 
        WHEN @cur_crew_type
        THEN @curRow := @curRow + 1
        ELSE @curRow := 0 END) + 1 AS cnt,
    @cur_crew_type := CONCAT(crew_id, type) AS cur_crew_type
FROM TABLE t,
     (SELECT @curRow := 0, @cur_crew_type := '') counter
      ORDER BY crew_id, type;

Row number per group in mysql

7 Answers7

Linked