Friday, March 30, 2007

Mimicking ROWNUM in DB2

I was going through the many random files stored on my PC when I came across this useful nugget. A year or so ago a reader sent me this code. He and his supervisor had come up with a way to simulate the Oracle rownum feature in DB2 and wanted to share it with the world. I think, at the time, I posted it up on TechTarget as I was doing Q+A there on the old search390.com site. But that site is now defunct, so I'm reposting the solution here on my DB2portal blog for all to share.



SELECT DEPTNO, ROW#
FROM DEDBADM.DEPT TB1,
TABLE (SELECT COUNT(*) + 1 AS ROW#
FROM DEDBADM.DEPT TB2
WHERE TB2.DEPTNO < TB1.DEPTNO) AS TDEPT_TAB
WHERE ROW# = 2;



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. That 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!


6 comments:

Craig S Mullins said...

There are additional examples of how to mimic ROWNUM in DB2 on this IBM web page.

Anonymous said...

The link does not work. Can you provide it again? Or can you provide me with some keywords to help me search on the ibm website?
thanks,
Brian Laube
p.s. I see a new function called row_number() in DB2 V9 on zOS.... but that is a long way away for me...

Craig S Mullins said...

Well, I am not sure when IBM took that page down, but I can no longer find it on the IBM web site. I did a quick search on ROWNUM but nothing popped.

Sorry about that.

Nash Crash said...

This code don't work. Maybe it isn't complete, because ther's an error in the HTML code!
Please, can you correct?
Thanks in advance

NashCrash said...

OK! I found the original code:

SELECT DEPTNO, ROW# FROM DEDBADM.DEPT TB1,
TABLE (SELECT COUNT(*) + 1 AS ROW#
FROM DEDBADM.DEPT TB2
WHERE TB2.DEPTNO < TB1.DEPTNO) AS TDEPT_TAB
WHERE ROW# BETWEEN 2 AND 4;

Anonymous said...

You can also use the following to limit results set to e.g. 100 rows.

FETCH FIRST 100 ROWS ONLY