topic How to get top 100 records? in Operating System - HP-UX

How to get top 100 records?

Hajime Kusakabe — Tue, 22 Jul 2003 18:53:39 GMT

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

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

Thanks!

Haji

Re: How to get top 100 records?

Volker Borowski — Tue, 22 Jul 2003 19:23:50 GMT

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

Re: How to get top 100 records?

Hajime Kusakabe — Tue, 22 Jul 2003 19:44:12 GMT

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

Haji

Re: How to get top 100 records?

R. Allan Hicks — Wed, 23 Jul 2003 01:26:12 GMT

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.

Re: How to get top 100 records?

Hajime Kusakabe — Wed, 23 Jul 2003 12:40:13 GMT

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

Haji