When OFFSET is 0, then no rows are skipped. If OFFSET is greater than the number of rows in the ordered results, then no rows are returned.
The OFFSET argument is used to identify the starting point to return rows from a result set. Basically, it exclude the first set of records. Note: OFFSET can only be used with ORDER BY clause. It cannot be used on its own.
OFFSET: This clause is used to specify the beginning point for returning rows from a result set. Basically, it ignores the initial set of records. SQL Server can use it only with the ORDER BY clause. If its value is negative, an error will be returned.
The ANSI SQL answer is FETCH FIRST . If you want ties to be included, do FETCH FIRST 10 ROWS WITH TIES instead. To skip a specified number of rows, use OFFSET , e.g. Will skip the first 20 rows, and then fetch 10 rows.
I would avoid using SELECT *
. Specify columns you actually want even though it may be all of them.
SQL Server 2005+
SELECT col1, col2
FROM (
SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
FROM MyTable
) AS MyDerivedTable
WHERE MyDerivedTable.RowNum BETWEEN @startRow AND @endRow
SQL Server 2000
Efficiently Paging Through Large Result Sets in SQL Server 2000
A More Efficient Method for Paging Through Large Result Sets
If you will be processing all pages in order then simply remembering the last key value seen on the previous page and using TOP (25) ... WHERE Key > @last_key ORDER BY Key
can be the best performing method if suitable indexes exist to allow this to be seeked efficiently - or an API cursor if they don't.
For selecting an arbitary page the best solution for SQL Server 2005 - 2008 R2 is probably ROW_NUMBER
and BETWEEN
For SQL Server 2012+ you can use the enhanced ORDER BY clause for this need.
SELECT *
FROM MyTable
ORDER BY OrderingColumn ASC
OFFSET 50 ROWS
FETCH NEXT 25 ROWS ONLY
Though it remains to be seen how well performing this option will be.
This is one way (SQL2000)
SELECT * FROM
(
SELECT TOP (@pageSize) * FROM
(
SELECT TOP (@pageNumber * @pageSize) *
FROM tableName
ORDER BY columnName ASC
) AS t1
ORDER BY columnName DESC
) AS t2
ORDER BY columnName ASC
and this is another way (SQL 2005)
;WITH results AS (
SELECT
rowNo = ROW_NUMBER() OVER( ORDER BY columnName ASC )
, *
FROM tableName
)
SELECT *
FROM results
WHERE rowNo between (@pageNumber-1)*@pageSize+1 and @pageNumber*@pageSize
You can use ROW_NUMBER()
function to get what you want:
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY id) RowNr, id FROM tbl) t
WHERE RowNr BETWEEN 10 AND 20
There is OFFSET .. FETCH
in SQL Server 2012, but you will need to specify an ORDER BY
column.
If you really don't have any explicit column that you could pass as an ORDER BY
column (as others have suggested), then you can use this trick:
SELECT * FROM MyTable
ORDER BY @@VERSION
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY
... or
SELECT * FROM MyTable
ORDER BY (SELECT 0)
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY
We're using it in jOOQ when users do not explicitly specify an order. This will then produce pretty random ordering without any additional costs.
For tables with more and large data columns, I prefer:
SELECT
tablename.col1,
tablename.col2,
tablename.col3,
...
FROM
(
(
SELECT
col1
FROM
(
SELECT col1, ROW_NUMBER() OVER (ORDER BY col1 ASC) AS RowNum
FROM tablename
WHERE ([CONDITION])
)
AS T1 WHERE T1.RowNum BETWEEN [OFFSET] AND [OFFSET + LIMIT]
)
AS T2 INNER JOIN tablename ON T2.col1=tablename.col1
);
-
[CONDITION] can contain any WHERE clause for searching.
[OFFSET] specifies the start,
[LIMIT] the maximum results.
It has much better performance on tables with large data like BLOBs, because the ROW_NUMBER function only has to look through one column, and only the matching rows are returned with all columns.
See my select for paginator
SELECT TOP @limit * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY colunx ASC) offset, * FROM (
-- YOU SELECT HERE
SELECT * FROM mytable
) myquery
) paginator
WHERE offset > @offset
This solves the pagination ;)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With