I have data:

OrderNumber, SomeValue

it returns in the following format:

23223, 110.1
23223, 234.5
23223, 65.5
38325, 999.4
38325, 436.3

ie I get multiple OrderNumbers with differing SomeValue fields after.

I want (in SQL) to return a dataset:

1, 23223, 110.1
1, 23223, 234.5
1, 23223, 65.5
2, 38325, 999.4
2, 38325, 436.3

ie start the counter at 1 and each time we move to a new Order number, increment the counter.

Obviously there are countless ways I could do this in Delphi, or through a multi-pass process (perhaps even a procedure in EDB!)

However is there any "trick SQL" which can achieve the same result more easily?

I am thinking of the fact that we have a "RUNSUM", and whether there is a way of implementing a similar "RUNCOUNT(aField)" ...

SELECT T2.*, D.SomeValue
FROM (SELECT Runsum(1), T.OrderNumber FROM (SELECT DISTINCT OrderNumber FROM InvoiceDetail) T
           GROUP BY T.OrderNumber ) T2
LEFT OUTER JOIN invoicedetail D ON T2.OrderNumber =D.OrderNumber

Sincere thanks, I will try this!