'MySQL Create View with Sequence Numbers for Each Group

I've seen a similar solution on this site but it's not usable in a view due to variable use limitations:

Generating Sequence for Each Group in MySQL

A table says a 1000 words so here goes:

I have this on a table:

Doc No  Rev
DOC-001 A01
DOC-001 A02
DOC-002 A01
DOC-002 B01
DOC-002 B02
DOC-003 Z01

I want this in a view:

Doc No  Rev Seq
DOC-001 A01 1
DOC-001 A02 2
DOC-002 A01 1
DOC-002 B01 2
DOC-002 B02 3
DOC-003 Z01 1

Please help!

If relevant: I use MySQL Workbench on Windows 10.

mysqlsqlwindow-functionssql-view


Solution 1:[1]

If you are using an older version of MySQL, then you may use a correlated subquery to generate the sequence:

CREATE VIEW yourView AS
SELECT
    DocNo,
    Rev,
    (SELECT COUNT(*) FROM yourTable t2 WHERE t2.DocNo = t1.DocNo AND t2.Rev <= t1.Rev) AS Seq
FROM yourTable t1
ORDER BY
    DocNo,
    Rev;

If you are using MySQL 8+, then use ROW_NUMBER:

CREATE VIEW yourView AS
SELECT DocNo, Rev, ROW_NUMBER() OVER (PARTITION BY DocNo ORDER BY Rev) Seq
FROM yourTable
ORDER BY
    DocNo,
    Rev;

Solution 2:[2]

If you are running MySQL 8.0, just use row_number():

select t.*, row_number() over(partition by doc_no order by rev) seq
from mytable t

If you are running an earlier version and you cannot use variables, then one option is a correlated subquery (although this will be far less efficient than window functions or variables):

select
    t.*,
    (select count(*) + 1 from mytable t1 where t1.doc_no = t.doc_no and t1.rev < t.rev) seq
from mytable t

Note that using this technique, ties would get the same seq - so this actually behaves like window function rank() rather than row_number().

Solution 3:[3]

Both answers are Working, but

CREATE VIEW yourView AS
SELECT DocNo, Rev, ROW_NUMBER() OVER (PARTITION BY DocNo ORDER BY Rev) Seq
FROM yourTable
ORDER BY
    DocNo,
    Rev;

is more accurate. This shows exactly is asked in the Question. The older style query does the same job but if more than One WHERE clauses are put into query then result is same as it is asked or we are expecting.

I have tested this logic using this complicated long query:

SELECT
    ROW_NUMBER() OVER (PARTITION BY Test_Centre ORDER BY Appliction_ID) Sr,
    Appliction_ID,
    Name_of_Applicant,
    Domicile, Gender,
    CONCAT(LEFT(CNIC, 5),'-',SUBSTRING(CNIC, 6, 7),'-',RIGHT(CNIC, 1)) AS CNIC,
    DATE_FORMAT(Date_of_Birth, "%d-%b-%Y") AS Data_of_Birth,
    DATE_FORMAT(FROM_DAYS(DATEDIFF('2020-08-11', Date_of_Birth)),'%Y') +0 AS Age,
    CONCAT(LEFT(Mobile_Number, 4),'-',SUBSTRING(Mobile_Number, 5, 7)) AS Mobile_Number,
    Test_Centre
FROM mt_applications
WHERE
    DATE_FORMAT(FROM_DAYS(DATEDIFF('2020-08-11', Date_of_Birth)),'%Y') >= 18 AND 
    DATE_FORMAT(FROM_DAYS(DATEDIFF('2020-08-11', Date_of_Birth)),'%Y') <= 27 AND 
    Name_of_Post = 'ASSISTANT (BPS-15)'
ORDER BY
    Test_Centre ASC,
    Appliction_ID ASC;

It's Working 100 Fine as Expectd :)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Tim Biegeleisen
Solution 2 GMB
Solution 3 ????? ????

Related Questions