Simulating row numbers -> Forum/discussion group
Abstract
These examples demonstrate how to simulate row numbers in your queries.
Content
The following SQL statements show how to simulate row numbers in your
queries:
Tip: In all of these examples, change the table qualifier as needed.
DSN8810 is for Version 8.
Example 1: The following query selects and numbers all rows in the sample DEPT table.
SELECT DEPTNO, ROW# FROM DSN8810.DEPT TB1, TABLE (SELECT COUNT(*) AS ROW# FROM DSN8810.DEPT TB2 WHERE TB2.DEPTNO < TB1.DEPTNO) AS TEMP_TAB;
Example 2: The following query selects only row 2 from the sample DEPT table.
SELECT DEPTNO, ROW# FROM DSN8810.DEPT TB1, TABLE (SELECT COUNT(*) + 1 AS ROW# FROM DSN8810.DEPT TB2 WHERE TB2.DEPTNO < TB1.DEPTNO) AS TEMP_TAB WHERE ROW# = 2;
Example 3: The following query selects only rows between row 2 and row 4 from the sample DEPT table.
SELECT DEPTNO, ROW# FROM DSN8810.DEPT TB1, TABLE (SELECT COUNT(*) + 1 AS ROW# FROM DSN8810.DEPT TB2 WHERE TB2.DEPTNO < TB1.DEPTNO) AS TEMP_TAB WHERE ROW# BETWEEN 2 AND 4;
Example 4: The following query selects the row immediately before and immediately after a particular row (the row where DEPTNO='E01') in the sample DEPT table.
SELECT * FROM DSN8810.DEPT
WHERE DEPTNO = 'E01'
OR DEPTNO = (SELECT MAX(DEPTNO) FROM DSN8810.DEPT WHERE DEPTNO < 'E01') OR DEPTNO = (SELECT MIN(DEPTNO) FROM DSN8810.DEPT WHERE DEPTNO >'E01') ORDER BY DEPTNO;
Example submitted by: Horace "Ed" Edwards, Florida Department of Education
Rate this example
User comments on this example:
"Great!"
"With this code I selectively displayed a range of records. Great code."
"I've been waiting for this code for five years. Great job!"
"Great code. This code has been a life saver!"
"Great code!"
"Why isn't this code in the z/OS and OS 390 systems? Outstanding!"
"Excellent code! Now I don't need to change the number of rows in SPUFI!"
"Wonderful! I found back records with this code!"
"I was able to locate the bad records in a table of 10 million rows easily.
They were rows 1,200,000 to 1,200,005. Outstanding code!"
"Excellent job. This code works in SPUFI and QMF."
"Brilliant! Now we have row numbers in DB2 for the OS390!"
"My friend tells me that even though we have OLAP functions (row_number()), this code works in all platforms that DB2 is running on! I wish that this code was available sooner! A million thanks!"
"Cool! I didn't know that "table" could be used in the select statement!"
"Wow! I was able to locate "bad" records in my sales table. Also, I didn't know you could use "count(*) + 1". Thanks!"
"Very Good! Even though there is not a rownum feature in the z/OS or OS390 systems, this code simulates the Oracle rownum feature. Thank you very much!"
"Excellent code! I used it to find the last 100 rows in a table with 3 million records!"
"Very, very good! And simple! By the way, has the word "table" always been a part of the "select" statement?"
"Yes!"
"I am crying! Could you give a brother a minute? It's soooooooo beautiful!
You see, I am running DB2 version 6 on the OS 390!"
"It works! As an Oracle DBA, you know I had to make "row#" the first field in the select statement. You know how we do it!"
"Before using this code, I could select the first row, the last row, a range of rows as long as I started with the first row. Now I can select rows 1,000 thru 1,015 and I can select the 1,000th row only! Brilliant!"
"Outstanding! We now have the "FETCH n ROWS" SQL clause in version 7.1 but you cannot select rows that doesn't include the first row or a range of rows that does not start with row one. Your code allows for this!"
"Very, very good! Now can you get them to include the OLAP functions in z/os and OS 390?"
"Outstanding! This works on DB2, OS 390, version 6."
"A million thanks! I am running DB2 Version 6 on a OS 390!"
"Magnificent! My supervisor said that there are no row numbers in OS390 for version 6. However, he is now using your code! Thanks!"
"Outstanding! When did the word "TABLE" become part of the "SELECT "
statement?"
"Magnificent! Now, can we get the "analytics" functions for OS390?"
"Touche! Your code helps in loading records and pagination!"
"Simple and easy to use! It does work on Version 8 on the OS/390!"
"It's takes too much resources to run."
"Extremely useful example. However it gets tedious if you have a multi-column primary key. Therefore my alternate method for this case: 1.
Declare a global temp table as clone and add an additional row# generated always as identity. 2. insert into clone select from table 3. Take the row# from the global temp"
"Bravo! It's working on OS/360, version 7.1!"
"All right! It works for me! I am running version 8, OS/390."
"Outstanding code! Can you get them to include the "TOP predicate as part of the SELECT statement?"
"Beautiful! I love it! Thanks!"
"Simple, but it works! Thanks."
"Ok! This is just what I needed! I am running version 6 on OS/390. By the way, the "TABLE" keyword began in version 6 and I didn't know about this feature!"
Notice: This example is from the DB2 Examples Trading Post and is provided on an "as-is" basis. You may copy and modify this example in any form without payment to IBM, for the purposes of designing and developing application programs conforming to the application programming interface for the z/OS or OS/390 operating system for which these examples are written. This example has been tested, but it has not been thoroughly tested under all conditions. IBM, therefore, cannot guarantee or imply reliability, serviceability, or function of this example in your particular environment. Use this example as a model for your own situations.
Return to the DB2 Examples Trading Post
P.S. Please pass my address to anyone interested in DB2 HOTLINE - thank you.
With kind regards
Michael Dewert, Software GroupTechnical Sales DB2
