Hey Suresh,
Your question: I am running DB2 version 8 on an OS/390 and I want to
retrieve the first 10 rows of a table and I want to also include row
numbers. We don't have a row number feature in DB2 version 8 for the
OS/390 mainframe. Can't it be done? Why, of course! Hehe.


SELECT ROWNUM, B.*
FROM DEDBADM.EMP B,
TABLE ( SELECT COUNT(*) AS ROWNUM
FROM DEDBADM.EMP4 AS A
WHERE A.EMPNO <= B.EMPNO
) TEMP_TAB
ORDER BY ROWNUM, EMPNO
FETCH FIRST 10 ROWS ONLY;


WITH
MATCHING_ROWS AS
(SELECT *
FROM EMPLOYEE
),
MATCH_ROW_NUMBERS AS
(SELECT M1.*
,(SELECT COUNT(*)
FROM EMPLOYEE M2
WHERE M2.EMPNO <= M1.EMPNO) AS TABLE_ROW#
FROM MATCHING_ROWS M1
)
SELECT TABLE_ROW#
,EMPNO
,LASTNAME
,FIRSTNME
,WORKDEPT
FROM MATCH_ROW_NUMBERS
WHERE TABLE_ROW# BETWEEN 1 AND 10
ORDER BY TABLE_ROW#;


It's Fridayyyyyyyyyyy! Partyyyyyyyyyyyy! Hehe.


Have a great Day!


Please take a few minutes to provide feedback on the quality of service you received from our staff. The Department of Education values your feedback as a customer. Commissioner of Education Dr. Eric J. Smith is committed to continuously assessing and improving the level and quality of services provided to you.Simply use the link below. Thank you in advance for completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]

DB2 & UDB email list listserv db2-l LazyDBA home page