Specifying an offset when using the TOP operator?

Specifying an offset when using the TOP operator?

Post by Peter Str?ima » Wed, 11 Dec 2002 17:43:04



Hi.

Like you can use the top operator,

eg.
select top 10 * from Table1
to get record no. 1-10 from the table, is there a way to get the NEXT 10
records?

A statement that selects records no. 11-20?

I searched BOL but can't find any answer

Thanks in advance,
Peter Str?iman

 
 
 

Specifying an offset when using the TOP operator?

Post by Tibor Karasz » Wed, 11 Dec 2002 17:49:41


Peter,

No. You can save the last returned value, though. That need an ORDER BY and you save the last
value and use that in your WHERE clause for the "next" SELECT statement.

--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...


Quote:> Hi.

> Like you can use the top operator,

> eg.
> select top 10 * from Table1
> to get record no. 1-10 from the table, is there a way to get the NEXT 10
> records?

> A statement that selects records no. 11-20?

> I searched BOL but can't find any answer

> Thanks in advance,
> Peter Str?iman


 
 
 

Specifying an offset when using the TOP operator?

Post by Itzik Ben-Ga » Wed, 11 Dec 2002 17:53:19


No built-in way; you have to devise your own, e.g.,

SELECT *
FROM (SELECT TOP 10 *
      FROM (SELECT TOP 20 *
            FROM T1
            ORDER BY key_col) AS A
      ORDER BY key_col DESC) AS B
ORDER BY key_col

Or...

SELECT TOP 10 *
FROM T1
ORDER BY key_col

-- keep the last key in a variable, then to get the next group of 10 rows
SELECT TOP 10 *
FROM T1

ORDER BY key_col

-- etc.

Or...

SELECT IDENTITY(INT, 1, 1) AS rownum, *
INTO #T
FROM T1
ORDER BY ...

CREATE UNIQUE CLUSTERED INDEX IDX_UC_rownum ON #T(rownum)

SELECT *
FROM #T
WHERE rownum BETWEEN 1 AND 10

SELECT *
FROM #T
WHERE rownum BETWEEN 2 AND 20

And so on...
--
BG, SQL Server MVP
Solid Quality Learning


Quote:> Hi.

> Like you can use the top operator,

> eg.
> select top 10 * from Table1
> to get record no. 1-10 from the table, is there a way to get the NEXT 10
> records?

> A statement that selects records no. 11-20?

> I searched BOL but can't find any answer

> Thanks in advance,
> Peter Str?iman