How to add several sequence numbers to one single sql view in MySQL
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!
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
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]