There is no a SQL standard to limit the number of records returned in a query, ie, each vendor implements this functionality in its own way.
Here's a summary on how to do this for some vendors:
DB2 -- select * from table fetch first 10 rows only
Informix, InterBase/Firebird, SKIP -- select first 10 * from table
Microsoft SQL Server and Access -- select top 10 * from table
MySQL, PostgreSQL, SQLite, HSQL/H2 -- select * from table limit 10
Oracle -- select * from table where rownum <= 10
Oracle Note:
If the query has "group by" or "order by" rownum must be supplied:
Ex:
SELECT a, b, c
FROM
( SELECT a, b, c, ROWNUM rn
FROM
( SELECT a, b, c FROM RECORD
WHERE AVAIL='Y"
ORDER BY DATE DESC
)
WHERE ROWNUM <= 25
)
WHERE rn >= 21;
FROM
( SELECT a, b, c, ROWNUM rn
FROM
( SELECT a, b, c FROM RECORD
WHERE AVAIL='Y"
ORDER BY DATE DESC
)
WHERE ROWNUM <= 25
)
WHERE rn >= 21;
References:
