|
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
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
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
HTH Vern
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
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.