Solved

# Calculate auto sequence number for grouped records in query

Posted on 2013-01-28
413 Views
I need to group records in a query and create a new column which assigns an auto-incrementing sequence number for the returned rows per group.

Using the example below, I group on ID and TYPE, sort by SEQ, and return an auto incrementing number for each group.

Example Data
ID     TYPE   SEQ   AUTOSEQ
001   M       1       1
001   N       2        1
001   N       3        2
001   M       4       2
001   N       5        3
002   M      1        1
002   M      2       2
002   N       3       1
0
Question by:Jinghui Li

LVL 119

Accepted Solution

Rey Obrero earned 500 total points
ID: 38828229
try this query

SELECT Table4.ID, Table4.type, Table4.seq, (select count(*) from table4 as t where t.id=table4.id and t.type=table4.type and t.seq<=table4.seq) AS AutoSeq
FROM Table4
ORDER BY Table4.ID, Table4.seq;
0

Author Closing Comment

ID: 38828366
Brilliant!

I'm trying to wrap my head around how that actually works - but it is perfect, thanks!
0

## Featured Post

### Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…