Query Sequence Numer

[MattBaldry]

Hi All,

Is there any way I can get a query to create a sequence number based on the first column, but start again when the data in the first column changes?

I have a query that has 511 rows. Column1 is BomReference and this has a total of 30 different BomRefences. What I am after is the query to generate the sequence number starting from 1, based on the the BomReference.

IE BP-000001-01000 is sequenced 1 to 17 and then when Column1 changes to BP-000001-01500 it starts at 1 again, through to 17 (or however many rows have that reference)

~Matt

 

[MajP]

Table1 Table1

ID	BOMreference
1​	IE BP-000001-01000
2​	IE BP-000001-01000
3​	IE BP-000001-01000
4​	IE BP-000001-01000
5​	IE BP-000001-01000
6​	IE BP-000001-01000
7​	IE BP-000001-01000
8​	IE BP-000001-01000
9​	IE BP-000001-01000
10​	IE BP-000001-01000
11​	BP-000001-01500
12​	BP-000001-01500
13​	BP-000001-01500
14​	BP-000001-01500
15​	BP-000001-01500
16​	BP-000001-01500
17​	IE BP-000001-01000
18​	IE BP-000001-01000
19​	IE BP-000001-01000
20​	IE BP-000001-01000
21​	IE BP-000001-01000
22​	IE BP-000001-01000

SELECT Table1.ID, Table1.BOMreference, (Select Count(ID) + 1 from table1 as B where B.bomReference = table1.bomreference and B.ID < table1.ID order by table1.ID ASC) AS Rank
FROM Table1
ORDER BY Table1.BOMreference, Table1.ID;

Query2 Query2

ID	BOMreference	Rank
11​	BP-000001-01500	1​
12​	BP-000001-01500	2​
13​	BP-000001-01500	3​
14​	BP-000001-01500	4​
15​	BP-000001-01500	5​
16​	BP-000001-01500	6​
1​	IE BP-000001-01000	1​
2​	IE BP-000001-01000	2​
3​	IE BP-000001-01000	3​
4​	IE BP-000001-01000	4​
5​	IE BP-000001-01000	5​
6​	IE BP-000001-01000	6​
7​	IE BP-000001-01000	7​
8​	IE BP-000001-01000	8​
9​	IE BP-000001-01000	9​
10​	IE BP-000001-01000	10​
17​	IE BP-000001-01000	11​
18​	IE BP-000001-01000	12​
19​	IE BP-000001-01000	13​
20​	IE BP-000001-01000	14​
21​	IE BP-000001-01000	15​
22​	IE BP-000001-01000	16​

 

[MattBaldry]

Thanks Maj

 

[ebs17]

Variant of the suggestion of @MajP :

SELECT
   T.ID,
   T.BOMreference,
   DCOUNT("*", "Table1", "BOMreference = '" & T.BOMreference & "' AND ID < " & T.ID) + 1 AS Rank
FROM
   Table1 AS T
ORDER BY
   T.BOMreference,
   T.ID

This is one of the few cases where using a domain aggregate function performs better than the subquery.
The fundamental problem of a correlated subquery (number of executions increases with record count of the main query and increases runtime almost exponentially) remains.

My personal limit is the application up to 10000 records of the main query. In addition, the time required is indisputably too high. There are then other solutions, albeit with some additional effort that has to be embedded in the overall task.

I have a query

A query instead of a table as the database brings with it its own effort for its execution. This exacerbates the described performance problem.