How to get top 100 records?

[Hajime Kusakabe]

What is the SQL command to retreive top 100 records from Allbase database?

I noticed there is no "TOP" or "ROWCOUNT".

Thanks!

Haji

[Volker Borowski]

Hello,
Not particulary Allbase,
but may be something from Informix or DB2 works... http://php.weblogs.com/portable_sql

Programmed found here ... http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20563735.html

Effective Code:

SELECT r.employee_id, r.name,
(SELECT COUNT(1) FROM employee WHERE employee_id <= r.employee_id) AS "RowNumber"
FROM employee r
ORDER BY r.employee_id ASC

But this could be an expensive statement for a big table.

Hope this helps
Volker

[Hajime Kusakabe]

Thank you for your reply but it does not seem work.
I don't see "COUNT" as a SQL commond for Allbase.

Haji

[R. Allan Hicks]

Under Oracle (I'm not sure if this is part of SQL90), there is a pseudocolumn called rownum.

Simply select your rows in descending order and set the where clause to where rownum < 100.

[Hajime Kusakabe]

Thank you for the reply.
But there is no "ROWNUM" command in AllBase either.

Haji