Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Fetch n rows without using rownum

Use this query to get n number of rows without using ROWNUM.

You may occasionally need to fetch n number of rows -- say 1 to 10 or 10 to 20 -- but DB2 doesn't have ROWNUM. So what to do? You can use this code, which has been tested on DB2 v6.1.

  
SELECT Q1.EMP_NAME,Q1.EMP_NO,Q1.EMP_SAL 
FROM  EMP_TBL AS Q1  
WHERE (Q1.EMP_NAME LIKE 'D%') 
  AND (Q1.EMP_DEPT = 'CS') 
AND 10 > (SELECT COUNT(Q2.EMP_NO) FROM   EMP_TBL Q2 WHERE  Q1.EMP_SAL <
Q2.EMP_SAL AND Q2.EMP_SAL IS NOT NULL)
 AND  0 <= (SELECT COUNT(Q2.EMP_NO) FROM   EMP_TBL Q2 WHERE  Q1.EMP_SAL <
Q2.EMP_SAL AND Q2.EMP_SAL IS NOT NULL)
ORDER BY Q1.EMP_SAL  DES

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free DB2 tips and scripts.
  • Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: DB2 tips, tutorials, and scripts from around the Web.

Dig Deeper on DB2 UDB (universal databases)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close