Re: SQL and retrieving the next xx rows

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

Bradley V. Stone wrote:
> When running an SQL statement on say, and order file, lets say you were
> letting the user enter an order number to search by used in the SQL
>
> ie...
>
> Select.....  where ordernum = searchorder
>
> Now, lets say that you wanted to also select the next 100 orders after the
> order number entered.  The problem being that the order numbers are not
> sequential.
>   

The joy of SQL.  There are several ways - almost as many as there are 
vendors.  Here's how you select the first five rows from the VENDORS table.

Oracle: SELECT * FROM VENDORS WHERE ROWNUM < 6
DB2: SELECT * FROM VENDORS FETCH FIRST 5 ROWS ONLY
SQL Server: SELECT TOP 5 * FROM VENDORS
  (same for Sybase)
Informix: SELECT FIRST 5 * FROM VENDORS
MySQL: SELECT * FROM VENDORS LIMIT 5
  (same for PostGRESQL)


Here's ANSI standard:

SELECT * FROM VENDORS A WHERE
 (SELECT COUNT(*) FROM VENDORS B WHERE B.VENPCT > A.VENPCT) < 5
 ORDER BY VENPCT DESC



Joe