MySQL Create View with Sequence Numbers for Each Group

Question

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.

Answer 1

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().