Operating System - HP-UX
1748170 Members
3950 Online
108758 Solutions
New Discussion юеВ

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 4
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