lorena2
asked on
Return Records 3-6
what's the syntax to return specific rows from a table? I don't want the top 3, I want the next 3....Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT TOP 3 *
FROM tablename
WHERE key NOT IN
(SELECT TOP 3 key
FROM tablename
ORDER BY key)
ORDER BY key
You will want to be sure that you have an index on key column(s) to use this method.
For a more general solution, you can create a scrollable cursor and FETCH ABSOLUTE the relative row number(s) you need. For example:
DECLARE @rownum INT
CREATE csr1 CURSOR SCROLL
FOR
SELECT ...
FROM tablename
ORDER BY ...
OPEN csr1
SET @rownum = 3
WHILE @rownum < 7 AND @@FETCH_STATUS <> -1
BEGIN
FETCH ABSOLUTE @rownum FROM csr1 INTO ...
--process row
SET @rownum = @rownum + 1
END