CODEWITHSUNDEEP
mysqlsql
Abe Miessler
Abe Miessler

Reputation: 85096

Possible to sequentially number groups in a sorted result set?

Say I have the following table:

id   | sub_id  | Name
----------------------
1    | 7       | Abe
1    | 90      | Bill
1    | 500     | Tom
2    | 1       | Gilbert
3    | 15      | Jose
3    | 18      | Don

Would it be possible for me to select all results and sequentially number the results based on the id? AKA, every time a new id is detected the sequence starts back at 0. Similar to SQL Server's RANK and DENSE_RANK functions. So the results would look like this:

id   | sub_id  | Name     | SequenceNumber
-----------------------------------------
1    | 7       | Abe      |1
1    | 90      | Bill     |2
1    | 500     | Tom      |3
2    | 1       | Gilbert  |1
3    | 15      | Jose     |1
3    | 18      | Don      |2

I can generate a number sequence that starts at 1 and ends at the last row number (6 in the above example) by doing something like this:

SELECT @row := @row + 1 as row, t.*
FROM my_table t, (SELECT @row := 0) r

But that isn't really what I need. Suggestions?

Upvotes: 0

Views: 52

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can use another variable to store previous id and compare it with the new id for this purpose i have use case statement to check for the new id,Note order by id is important to assign a correct sequence number

select t.*,
@row:= case when @group = t.id then @row + 1 else 1 end SequenceNumber,
@group:=t.id
from test t
cross join (SELECT @group:=null, @row := 0) r
order by t.id

Demo

For having only the desired fields see below demo

Demo 2

Upvotes: 1

Related Questions