How to get top 1000 rows

[JimWWhite]

I can connect to an AS/400 DB2/400 database running on an I-Series server using an ODBC connection to the libary and tables. If I submit "SELECT * FROM tablename" as a query I get back all rows in the table. However, I'd like to run a query that limits the number of rows as can be done in MS-SQL by doing a "SELECT TOP 1000 * FROM tablename". There is no single column defined on the table as the primary key that is an integer value. Can someone provide me with the proper syntax for selecting the first or top n rows from an AS/400 table?

Many Thanks!
Jim

[sakumar9]

You can use FETCH FIRST n ROWS ONLY clause.

SELECT EMPNAME, SALARY FROM EMPLOYEE
   ORDER BY SALARY DESC
   FETCH FIRST 100 ROWS ONLY

Regards
-- Sanjay