How to add several sequence numbers to one single sql view in MySQL

Question

what I would like to do is to add several sequence numbers to one single sql view in MySQL. Consider the following example table:

Folder 1, File 1
Folder 1, File 2
Folder 2, File 3
Folder 3, File 4
Folder 3, File 5
Folder 3, File 6

What I would like to get as a result would be:

1, Folder 1, File 1
2, Folder 1, File 2
1, Folder 2, File 3
1, Folder 3, File 4
2, Folder 3, File 5
3, Folder 3, File 6

I know how I can add one single sequence number to the whole view using variables but I have no idea how to solve my specific problem. Hopefully someone can help me with that.

Thanks a lot!

Cheers, Johannes

RichardTheKiwi · Accepted Answer · 2011-02-07 10:18:12Z

2

Instead of using one single variable you will need two, reset the row numbering whenever the group changes.

select folder, file,
  @r = case when @g = folder then @r+1 else 1 end SequenceNo,
  @g := folder
from (select @g:=null) g
cross join tbl
order by folder, file

answered Feb 7, 2011 at 10:18

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to add several sequence numbers to one single sql view in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related