RE: Numbering rows in SQL result set

Try it on a platform that you only have SQL to do it with.

John Brandt
iStudio400.com

-----Original Message-----
From: M. Lazarus [mailto:mlazarus@xxxxxxxx]
Sent: Wednesday, May 05, 2004 11:47 PM
To: Midrange Systems Technical Discussion
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

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.665 / Virus Database: 428 - Release Date: 4/21/04
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.676 / Virus Database: 438 - Release Date: 5/3/04