Re: Numbering rows in SQL result set

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

It is possible with a stored procedure, I think. Maybe what we call 
user-defined table functions. Do the lookup on google, there were 
interesting examples there.

There was an example that did not need anything too advanced. Only that a 
SELECT is used for one of the columns, and that takes V5R1, I think, if not 
later. It also depends on unique values in the ORDER BY column aggregate. 
Here's the example over the TPC-H benchmark ORDERS table (1,600,000 
records) - modify to suit:

SELECT O_CUSTKEY,
             O_ORDERKEY,
             (SELECT COUNT(*)
                  FROM TPCH/ORDERS O2
                  WHERE O2.O_ORDERKEY <= O.O_ORDERKEY
                       AND O2.O_CUSTKEY = 10) AS ROWNUMBER
     FROM TPCH/ORDERS O
     WHERE O_CUSTKEY = 10
     ORDER BY O_ORDERKEY

This gave the following:

O_CUSTKEY      O_ORDERKEY       ROWNUMBER
       10          36,422               1
       10         816,323               2
       10         859,108               3
       10         883,557               4
       10         895,172               5
       10         916,775               6
       10       1,490,087               7
       10       1,774,689               8
       10       2,126,688               9
       10       2,917,345              10
       10       3,069,221              11
       10       3,354,726              12
       10       3,487,745              13
       10       3,580,034              14
       10       3,916,288              15
       10       3,942,656              16
       10       4,141,668              17
       10       4,243,142              18
       10       4,407,621              19

To quote, "this is a good solution if you have a field that uniquely 
identifies a record AND you want to order by that field." I might add, if 
your ORDER BY fields uniquely identify a record. Then the "<=" would need 
to use the aggregate of the sort fields (concatenate them).

This is likely to be quite slow - the UDTF is probably better. The 
following (from Google) might do the job. I've not worked on these at all, 
yet. Some of the syntax needs to be changed. This requires V5R2. (The UDTF 
could also be an RPG that reads the file and adds a sequence number.)

CREATE TABLE #RowNumber (
RowNumber int IDENTITY (1, 1),
emp_id char(9) )

INSERT #RowNumber (emp_id)
SELECT emp_id
FROM employee
WHERE job_id = 10
ORDER BY lname

SELECT RowNumber, e.emp_id, lname, fname, job_id
FROM #RowNumber r JOIN employee e
ON r.emp_id = e.emp_id
ORDER BY RowNumber

DROP TABLE #RowNumber

HTH
Vern

At 01:53 PM 5/5/2004, you wrote:
> This is what I wound up doing (in Net.Data).  I was curious if I could
> have let SQL do it instead of doing it myself.
>
> Mike E.
>
> James Rich <james@xxxxxxxxxxx>
> Sent by: midrange-l-bounces@xxxxxxxxxxxx
> 05/05/2004 01:37 PM
> Please respond to Midrange Systems Technical Discussion
>
>         To:     Midrange Systems Technical Discussion
> <midrange-l@xxxxxxxxxxxx>
>         cc:
>         Subject:        Re: Numbering rows in SQL result set
>
>
> On Wed, 5 May 2004 meovino@xxxxxxxxxxxxxxxxx wrote:
>
> > Is there an SQL scalar function or some other trick I can use to add a
> > column to a result set that numbers the rows in the set.  Let's say my
> > result set currently is:
>
> If you are reading the SQL results in a program just create a counter that
> increments each time you retrieve a row.  You are probably doing this
> already if you are using a multiple occurance DS.  If you aren't reading
> the results in a program then obviously that won't work ;)
>
> James Rich