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


Problem :

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



Solution :

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

Mysql Tutorials

Mysql Howto..

how to check if there exists 2nd highest score in SQL?

How to generate a big number of unique random numbers in MySQL

MYSQL - insert into and a null value, how to avoid?

How to query soundex() in mysql

How to prevent multiples instances of a script?

Swift/MySQL/PHP: how to write an ios app using an online database [closed]

How to select N records from a table in mysql

How to get the last record in MySql with 2.5m rows

MySQL: How to get user with most wins?

How to make Doctrine use a MySQL TIMESTAMP column instead of DATETIME?

How to let a user manipulate text (make it bold/italicized), before it is saved in a MySQL table?

How to display MySQL data in a list

mysql how to query where ROUND(AVG(`row`)) equals

how to skip the inverted commas when selecting a column from MySQL

How to load the rest of the data in mysql in php

How to insert data which does not have specific sequence?

How to represent “Recurring Events” in MySQL database?

How to rename a MySQL column from long to something else

How to limit MySQL query result to specific results?

How to restore a mysql database in php

How to group number of rows per day also in no-row days in MySQL?

Flask and Python how to make search engine for data from mysql database

MySQL How to order a Joined and Group_Concat column?

how to enter events for every third friday of every month in php/mysql

How to extract data from mysql to a csv file?

How to see table partition size in MySQL ( is it even possible? )

How do I setup MySQL queries to receive Backbone pagination requests?

How facebook store and access text data

PHP/MYSQL - MYSQLI - Prepared Statements - How to get the value of this query?

How to Work a JQUERY function after checking the MYSQL in PHP