Fri 27 May 2005
One of the thngs I like about my job is solving problems. In particular problems where there is a bit of thought involved. The particular problem I had is that for a search interface I’m building a requirement is the ability to page result set. This is easy using MySQL as it has a LIMIT keyword that limits the results to a certain number, and allows an offset, so for example you to retrieve results 16-20 you would use LIMIT 15,5.
Unfortunately, SQL Server does not have an equivalent to the offset argument. Its nearest is TOP n, which returns the first n rows.
This puzzled me for a few days, but then the answer came to me in a flash of inspiration: Results 20-16 would be the first 5 rows of a TOP 5 statement, with a nested query with a TOP 20 statement in the opposite order. Then I could just reverse the order to get the result the right way around.
So here’s what I came up with:
SELECT * FROM (
SELECT TOP x * FROM (
SELECT TOP y fields
FROM table
WHERE conditions
ORDER BY table.field ASC) as foo
ORDER by field DESC) as bar
ORDER by field ASCx is the number of rows you want returned and y is x+offset.
After looking at this in Query Analyzer, it appears that the extra nesting does not add very much to the load. For the queries I was running it was only about 2%.
48 Responses to “Paging results in MS SQL Server”
Leave a Reply
You must be logged in to post a comment.
July 21st, 2005 at 02:42
It looks like you have three closing parentheses, and only two opening in your query.
July 21st, 2005 at 06:54
Thanks. It’s fixed now.
July 28th, 2005 at 08:15
I’ve tried implementing this kind of technique, but I’m having some weird results, and I figured out that it has to do with non-unique sort fields.
If you sort on a field that is not unique, then the sort order is somewhat arbitrary. For example, consider the following data set:
Rank Name
100 Bob
100 Joe
90 Sally
90 Sue
80 Bobby
70 Jim
70 Nancy
Let’s say that you want to page the results into groups of 3. For the first page, you get Bob, Joe, and Sally. However, when you reverse the sorting on the top 6 (to get page 2), you get this:
Rank Name
70 Nancy
80 Bobby
90 Sally
90 Sue
100 Bob
100 Joe
The top 3 of those come up as Nancy, Bobby, and Sally. Then, reverse the sorting again (back to the cannonical order) and you end up with Sally, Bobby, Nancy on the second page. However, Sally already appeared on the first page, and Sue doesn’t show up anywhere!
Of course, if you have unique sort fields, then this isn’t a problem at all, since your sort order is entirely deterministic. Just thought I’d point out that this method isn’t *quite* the same as MySQL’s LIMIT statement.
When you use Full Text Searching in MSSQL, you typically end up with a Rank value that is nondeterministic, and you definitely want to sort on that field (to show more relevant hits first.) It is quite possible for two items to have the same rank, so you could end up with weird results if you attempt to page the results using this method.
Other than that, this is very clever. Nice job
July 28th, 2005 at 08:23
Great point Isaac. Fortunately for me the fields I sort by are unique.
August 12th, 2005 at 23:21
I think there is a much simplier method. Check this SQL statement:
SELECT TOP number_of_items_on_page *
FROM table
WHERE id NOT IN (
SELECT TOP offset id FROM table ORDER BY id)
ORDER BY id;
Where:
“id” is your primary key for the table.
“number_of_items_on_page” number of results displayed on page
“offset” starting offset
August 31st, 2005 at 20:23
thanks a lot for you and GroM
November 7th, 2005 at 16:54
GroM
your query wors fine, but not for large tables and not for large number of rows per page (i.e.100). Reason for that is in size of query string which maximum is something like 8 kb. It will still work probably for firs 4-5 pages, but things get complicated when list for IN statement gets very long and enire query gets long. at some stage i have a feeling it will cause an error.
November 8th, 2005 at 06:28
for isaac question:
you can add something like:
+ cast(id as varchar(50))
to the order clauses to prevent that
November 23rd, 2005 at 02:32
skodman is correct…although i havent seen it error out yet, as you go from page to page the query gets slower and slower.
November 28th, 2005 at 03:18
Thank you Joe, This artical really helped me out. I’m not used to MSSQL Databases. Keep up the good work! : P
December 2nd, 2005 at 09:51
Joe…I have a little problem. Lets say I do a query and I return 43 rows. And lets say the site puts 10 rows on each page. I’m going to need 5 pages to show everything. On the 5th page I should only have 3 results shown but instead im getting the last three and the previous 7 to fill the page…The query is in order Im just repeating 7 results from the 4th page. I was wondering if you knew what to do to fix this.
December 2nd, 2005 at 23:00
Hi Foxhop,
I think this would fix your problem…
SELECT TOP (x+z) IDENTITY(int,1,1) as idx, fields
INTO #tmpTable
FROM table
WHERE conditions
ORDER BY table.field ASC
SELECT * FROM #tmpTable WHERE idx>z
where x is the number of rows you want per page, and z is the offset (zero based).
Let me know if this works; email or post another comment.
December 20th, 2005 at 09:29
The original method with multiple ordering is really interesting. Unfortunalty, SQL Server 2000 and probably 98 does not allow a variable to be used with the TOP keyword. All the tradoff to this are explain here: http://www.aspfaq.com/show.asp?id=2521 . I wanted to use your method in a stored procedure, but it seems like there is no elegant solution.
December 28th, 2005 at 17:51
I’m relatively new to SQL Server but this is what I came up with (requires an autonumber column to work):
Select TOP 20 * From [Table] WHERE ID > (SELECT TOP 1 x FROM (SELECT TOP 1000 ID as x FROM [Table] ORDER BY ID) as x ORDER BY x DESC)
20 is the Page Size and 1000 is the Offset
December 30th, 2005 at 08:01
To address Isaac’s concern, you can disambiguate the sort by adding an ID field as a secondary field to all of the ORDER BY clauses.
For example, replace:
ORDER BY field ASC
with
ORDER BY field ASC, id_field ASC
I didn’t quite understand jorgempf’s comment until I wrote this, but he’s essentially recommending something similar (but not quite as nice, IMHO
January 13th, 2006 at 07:46
Microsoft has a write-up on this very issue.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/scalenethowto05.asp
January 13th, 2006 at 21:46
A little tip - if you want to massage the data (like concatinate fname and lname), do it on the outer most select - not on an inner most.
That’s because the outer most select will return the least amount of rows.
January 17th, 2006 at 17:37
I have a problem
I have a long table with many costumers, each costumer have between 70-100 bills how can i get for each one his top 5, all the replies giving me only the top 5 of the first.(MsSQL!)
10X Ofir
March 18th, 2006 at 10:19
This is a neat solution. But for a someone new to ASP and MS-SQL, how do you go forward and back?
April 1st, 2006 at 09:27
This is what worked for me
20 is limit
5 is offset
select top 20 * from categories
where categoryid >=
(Select Top 1 CategoryID from
(Select top 5 CategoryID from categories order by CategoryID ASC) as foo order by categoryid desc
)
April 9th, 2006 at 06:47
But how do you parameterize TOP clause? At least ‘y’ the offset in the result set, should be a parameter in a paging procedure like this. Can you use TOP with parameters somehow?
May 17th, 2006 at 05:46
It seems it is solved in SQL Server 2005, http://weblogs.asp.net/Firoz/archive/2005/06/12/411949.aspx
June 25th, 2006 at 18:11
I just want to know the better/simpler practice, beggining from joe’s solution with support for no PK (isaac’s question), and also solving FoxHolp problem (the return of the last page) trying not to use temp tables.
Thanks you.
June 25th, 2006 at 18:51
Thank you so much (everyone and Joe in particular.) I’m writing an AJAX front-end for my company’s database and SQL Server has been nothing but a pain. This post is one of bright lights in the whole affair. At least it’s possible to page through a database in SQLServer. It’s ugly and requires an IDENTITY field (i.e. is not table independent) but at least SQLServer can do this.
One problem (though not insurmountable) in the original code is that it doesn’t work with an offset of x
June 25th, 2006 at 18:58
where x is less than y. The only solution to this that I was able to find is to code the application to handle this special case.
And as far as ROW_NUMBER() being a solution as juan points out, this still requires an IDENTITY column AFAICT.
Please, please, Microsoft, please add LIMIT to SQLServer! I will make sure that every installation I come in contact with is upgraded immediately if you do. If you don’t like that it was NIH then call it something else. Hell, call it POSTGRESISOBTUSE or MYSQLISNOTAREALDATABASE. I don’t care, just make your product do it.
July 9th, 2006 at 03:40
The ideas presented here are great, but didn’t help me that much because of the inability to put a parameter in the TOP statement. I am not creating SQL in my application, I am using stored procedures. I figured out how to write a stored procedure to give me the paging capability that I needed.
Assume we have a table as follows:
CREATE TABLE Employee
(
EmployeeID int IDENTITY(1,1) NOT NULL,
EmployeeName nvarchar(50) NOT NULL,
EmployeeEmail nvarchar(60) NOT NULL
)
And that we want to page through the employees
sorted by Email address.
Here is how to write a stored procedure to do it:
———————-
– begin sproc script
———————-
CREATE PROCEDURE Employee_GetPagedData
@offset int = 0,
@numrows int = 25
AS
SELECT
IDENTITY(int, 1,1) AS RowNum,
CAST(EmployeeID AS int) AS EmployeeID,
EmployeeName,
EmployeeEmail
INTO #tempEmployee
FROM Employee WITH (NOLOCK)
ORDER BY EmployeeEmail
SELECT *
FROM #tempEmployee
WHERE RowNum > @offset
AND RowNum
July 9th, 2006 at 03:43
got clipped…
————————
–- begin sproc script
————————
CREATE PROCEDURE Employee_GetPaged
@offset int = 0,
@numrows int = 25
AS
SELECT
IDENTITY(int, 1,1) AS RowNum,
CAST(EmployeeID AS int) AS EmployeeID,
EmployeeName,
EmployeeEmail
INTO #tempEmployee
FROM Employee WITH (NOLOCK)
ORDER BY EmployeeEmail
SELECT *
FROM #tempEmployee
WHERE RowNum > @offset
AND RowNum
July 9th, 2006 at 03:50
trying again with html encoding… :s
CREATE PROCEDURE Employee_GetPaged
@offset int = 0,
@numrows int = 25
AS
SELECT
IDENTITY(int, 1,1) AS RowNum,
CAST(EmployeeID AS int) AS EmployeeID,
EmployeeName,
EmployeeEmail
INTO #tempEmployee
FROM Employee WITH (NOLOCK)
ORDER BY EmployeeEmail
SELECT *
FROM #tempEmployee
WHERE RowNum > @offset
AND RowNum <= (@offset + @numrows)
ORDER BY EmployeeEmail
DROP TABLE #tempEmployee
GO
July 26th, 2006 at 18:59
This is a great thread! I was struggling with this same problem myself. Joseph’s solution is rather elegant, but for SQL Server 2005, the post that Juan referenced gives an easier solution. Thanks to all of you for posting!
July 29th, 2006 at 19:43
Nice thread, will solve some of my dev teams issues, Juan’s post for SQL Server 2005 has a good & quick solution. Thanks for all the info.
September 6th, 2006 at 20:18
thanks to sophisticated.co.th, i am also using sprocs. excellent info.
September 7th, 2006 at 16:22
Thanks Joe,
Nice and Simple.
September 9th, 2006 at 14:56
thanks joseph, u save me with this trick.
by the way, is this solution tested in a milion of data?
September 20th, 2006 at 04:14
Neat trick…I’m redoing a page that would call the entire recordset, and then set up a loop to move the cursor to the correct record. The only problem I found with it is that in ASP classic, the .RecordCount property returns -1.
September 22nd, 2006 at 01:15
Why don’t you use cursor?
October 4th, 2006 at 00:59
Hi,
There is a table tblSector has contain two column SectorId, SectorName. I have to display result orderby SectorName but SectorId “1″ will be on top. where SectorId is integer and SectorName is Varchar()
what i do for this..
October 24th, 2006 at 09:28
Using SET ROWCOUNT dismiss the use of temporary tables and allows parametrization of page len and offset.
Suppose I have pages with 10 records per page and I want the second page:
–@offset must be >= 1
SET ROWCOUNT @offset
SELECT @offset_id = employeeID FROM employees ORDER BY employeeid
– Now, set the row count to pagelen
SET ROWCOUNT @pagelen
SELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
e.DepartmentID = d.DepartmentID
WHERE employeeid >= @offset
ORDER BY e.EmployeeID
–Back to the mortal world.
SET ROWCOUNT 0
===========8>=====================
As seen on:
http://www.4guysfromrolla.com/webtech/042606-1.shtml
October 24th, 2006 at 09:37
A typo. Last query should be @offset_id instead of @offset:
SELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
e.DepartmentID = d.DepartmentID
WHERE employeeid >= @offset_id
ORDER BY e.EmployeeID
November 14th, 2006 at 14:01
Great post, thanks. Has revolutionised some paging of very large datasets we have been working on.
We did find that the performance degraded as you progessed along the dataset.
The solution was to:
a) Move the fields you want to return into the outer select.
b) Have only the sort field(s) and unique key for each record returned by the inner filtering select
c) Ensure there is a covering index for all your filter and sort columns.
eg:
SELECT fields FROM (
SELECT TOP x * FROM (
SELECT TOP y id_field, sort_field
FROM table
WHERE conditions
ORDER sort_field ASC) as foo
ORDER by sort_field DESC) as bar
INNER JOIN table ON bar.id_field = table.id_field
ORDER by sort_field ASC
This way SQL is deciding what records to select from the index rather that querying the table. It also isn’t required to hold a long, bulky dataset in memory while it sorts. It only hits the table when it has a page worth of rows.
Thanks again.
Adam
http://www.esendex.com
January 29th, 2007 at 11:22
Hi there!
This is the procedure I came over after the many samples. You simple have to pass a filter (this was in my case) plus the page number. Hope it helps!
ALTER PROCEDURE SP_CENARIO_LIST 1, 0
@ID_CENARIO tinyint,
@page int = 0,
@numrows int = 10
AS
DECLARE @offset int
SET @offset = (@page * @numrows)
– Whole select
SELECT
IDENTITY(int, 1,1) AS RowNum,
CAST(CD_PERSONAGEM AS int) AS CD_PERSONAGEM,
DE_ARQUIVO,
DT_CADASTRO
INTO
#temp
FROM
T001_PERSONAGEM
WHERE
FL_OPCAO = @ID_CENARIO
AND
FL_ATIVO = 1
AND
FL_PUBLICAVEL = 1
ORDER BY
DT_CADASTRO ASC
– Paging
SELECT
*
FROM
#temp
WHERE
RowNum > @offset
AND
RowNum
January 29th, 2007 at 11:37
Oh, oh, it seems the system doesn’t allow me to paste all the code. Nevermind, a did a few improvement, reducing the “overhead” for the first select. If you want to take a look, I put a copy of this script at http://www.asteria.com.br/paging.txt
Cheers!
April 14th, 2007 at 11:10
Let me start by saying that Microsoft SQL Server 2000 is Satan’s gift to the world.
I’ve been using my programming language (php) to solve Foxhop’s problem (#10), without the use of stored procs or temp tables:
$x = number of rows per page
$y = x * page number
$count = “SELECT count(*) FROM table WHERE conditions”;
if ($count
April 17th, 2007 at 05:06
Again, html encoded…
$x = number of rows per page
$y = x * page number
$count = $dbh->query(”SELECT count(*) FROM table WHERE conditions”);
if ($count < $y){
$x = $count % $x
}
Then plug $x, $y into the original formula:
SELECT * FROM (
SELECT TOP x * FROM (
SELECT TOP y fields
FROM table
WHERE conditions
ORDER BY table.field ASC) as foo
ORDER by field DESC) as bar
ORDER by field ASC
May 8th, 2007 at 07:45
Hi folks,
if you use SQL 205 there is a much easier and faster solution. If you have more than 100.000 records, this here is a few seconds faster than the other solutions. In fact its pretty the same what mySQL does in the background, but its faster.
SELECT *
FROM (SELECT ROW_NUMBER()
OVER (ORDER BY Your_Data_ID DESC)
AS Row, *
FROM Your_Data)
AS WithRowNumbers
WHERE Row >= 1 AND Row
May 8th, 2007 at 22:48
ok, second part:
WHERE Row>=1 AND Row=100 AND Row
May 8th, 2007 at 22:50
this editor s…. you can reach me here if you have questions http://www.digital-ecom.de cu folks
May 26th, 2007 at 09:24
this paging script is great, until you would like to dynamically sort by a field in the inner table.
does anyone have a script that can handle this?
June 1st, 2007 at 03:28
wish you could edit previous posts..
anyway, forget the word “dynamic”, just need to sort by a field in the inner table. any ideas?