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

Amen to that.  I was figuring that my lack of knowledge of SQL was keeping 
me from a solution that would take just a line or two of code (as it 
always seems to do).  Oh well, it sounds like they're taking care of it in 
i5/OS.  Thanks everyone!

Mike E.






"M. Lazarus" <mlazarus@xxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
05/06/2004 12:47 AM
Please respond to Midrange Systems Technical Discussion
 
        To:     Midrange Systems Technical Discussion 
<midrange-l@xxxxxxxxxxxx>
        cc: 
        Subject:        Re: Numbering rows in SQL result set


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