Re: Numbering rows in SQL result set -- MIDRANGE-L

Vern, et al,

Does anyone else think that the complex code required to do something this simple and basic in SQL is absurd? I love SQL for some things, but the hoops required to jump through for some other items makes it a clumsy solution many times on the /400. JMHO.

-mark

At 5/5/04 04:57 PM, you wrote:

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