cancel
Showing results for 
Search instead for 
Did you mean: 

How to get top 100 records?

Hajime Kusakabe
Occasional Contributor

How to get top 100 records?

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

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

Thanks!

Haji
4 REPLIES
Volker Borowski
Honored Contributor

Re: How to get top 100 records?

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
Occasional Contributor

Re: How to get top 100 records?

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
Trusted Contributor

Re: How to get top 100 records?

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.
"Only he who attempts the absurd is capable of achieving the impossible
Hajime Kusakabe
Occasional Contributor

Re: How to get top 100 records?

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

Haji