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.