Q
Problem solve Get help with specific problems with your technologies, process and projects.

Rownum code solution for DB2 on the OS/390

Craig,

My supervisor and I figured out how to simulate the Oracle rownum feature in DB2, version 7.1 , on the OS/390. Here it is!

SELECT DEPTNO, ROW# FROM DEDBADM.DEPT TB1,
    TABLE (SELECT COUNT(*) + 1 AS ROW#
  FROM DEDBADM.DEPT TB2
  WHERE TB2.DEPTNO < TB1.DEPTNO) AS TEMP_TAB
  WHERE ROW# = 2;
Could you share this code with the world for us? Thanks.
Sure, I'd be happy to share this code with the world. If you decide to use this code, be careful because there is no guarantee that the row numbers will be the same from run to run of the SQL. As long as you get the same access path the row numbers should be the same from run to run, but if the access path changes -- for example, from a table space scan to indexed access or vice versa -- then the row numbers for the runs will differ. Tthat is, each row may have a different number than it had for a previous execution.

All in all, though, a nice solution to the problem!

Anyone looking for some more examples of row number DB2/SQL check out this site.

Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our .VO7aaqqaAFk.0@/search390>discussion forums.

This was last published in August 2004

Dig Deeper on IBM DB2 management

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation

1 comment

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

I Guess this Query works only for unique rows.
Hi this is working only for Unique values, for the duplicate values it is not working.
Assume, If the same DEPT number is existing for the 2 or more than 2 rows this query partially working. Not Exactly working.
Cancel

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSQLServer

Close