RE: SQL and retrieving the next xx rows

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Joe Pluta
Sent: Tuesday, July 08, 2008 11:09 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL and retrieving the next xx rows


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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.