Well, you might not like my answer, but here goes.

First of all, you need to refresh your knowledge of "relational" DBMS. There really is no such thing as the "top" or "bottom" N rows in a table. With regard to the result set, though, there is a top and a bottom. You can use the FETCH FIRST N ROWS ONLY clause to retrieve only the first N rows, but to retrieve only the bottom N rows is a more difficult problem. For that, you would have to use scrollable cursors. A scrollable cursor allows you to move back and forth through the results set without first having to read/retrieve all of the rows before. I suggest that you read up on scrollable cursors in the DB2 SQL Reference manual and the DB2 Application Programming manual (all DB2 manuals can be downloaded from the Web for free - start here. Basically, you would want to FETCH LAST from the scrollable cursor and then loop through with a FETCH PRIOR statement executing the loop N-1 times. That would give you the "bottom" N of any results set (sorted or not).

As for your other question, I am confused as to why you would want to delete N rows from a table. Doesn't it matter what the data in the rows is? My guess is that you are asking how you would limit a delete to a subset of the rows that would apply to the WHERE condition of the DELETE. The answer is, you cannot. You would have to open a cursor with the same WHERE conditions specifying FOR UPDATE OF. Then you would FETCH and DELETE WHERE CURRENT OF cursor for that row in a loop that occurs N times. Of course, that means you have to write a program.

Hope this helps!

• DB2 > DB2 with Craig Mullins

• Operating Systems
• IT Management
• Networking
• Database
• Security
• Servers and Storage
• Applications and Development
• Career Center

		Expert Answer Center: Expert Q&As