Limiting Returning Recordset to X rows

[phillyman]

I'm setting up an ASP page that makes a call to a DB2 database of banking transactions. I'd like to display the last 10 transactions by date for a particular account number. In SQL Server, I use the "SELECT TOP 10" syntax to load a recordset with the top 10 rows by date but this syntax does not work in DB2.
I'd prefer not to pull down the entire recordset of transactions by account number and only pull down what I need so I'd rather not use a cursor and the FETCH command.

Anyone have any ideas??

Thanks,
Scott Wilkinson

 

[K9Boxer]

Check out:

http://www-4.ibm.com/cgi-bin/db2www...port/document.d2w/report?fn=db2v7s0fet1st.htm

.-1-------.
>>-FETCH FIRST--+---------+---+-ROW--+--ONLY-------------------><
'-integer-' '-ROWS-'


The fetch-first-clause sets a maximum number of rows that can be retrieved. It lets the database manager know that the application does not want to retrieve more than integer rows, regardless of how many rows there might be in the result table when this clause is not specified. An attempt to fetch beyond integer rows is handled the same way as normal end of data (SQLSTATE 02000). The value of integer must be a positive integer (not zero).

Limiting the result table to the first integer rows can improve performance. The database manager will cease processing the query once it has determined the first integer rows. If both the fetch-first-clause and the optimize-for-clause are specified, the lower of the integer values from these clauses will be used to influence the communications buffer size. The values are considered independently for optimization purposes.

Specification of the fetch-first-clause in a select-statement makes the cursor not deletable (read-only). This clause cannot be specified with the FOR UPDATE clause.

 

[malati]

select * from tablename fetch first 10 row only