ChatGPT解决这个技术问题 Extra ChatGPT

Implement paging (skip / take) functionality with this query

I have been trying to understand a little bit about how to implement custom paging in SQL, for instance reading articles like this one.

I have the following query, which works perfectly. But I would like to implement paging with this one.

SELECT TOP x PostId FROM ( SELECT PostId, MAX (Datemade) as LastDate
 from dbForumEntry 
 group by PostId ) SubQueryAlias
 order by LastDate desc

What is it I want

I have forum posts, with related entries. I want to get the posts with the latest added entries, so I can select the recently debated posts.

Now, I want to be able to get the "top 10 to 20 recently active posts", instead of "top 10".

What have I tried

I have tried to implement the ROW functions as the one in the article, but really with no luck.

Any ideas how to implement it?


R
Radim Köhler

In SQL Server 2012 it is very very easy

SELECT col1, col2, ...
 FROM ...
 WHERE ... 
 ORDER BY -- this is a MUST there must be ORDER BY statement
-- the paging comes here
OFFSET     10 ROWS       -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows

If we want to skip ORDER BY we can use

SELECT col1, col2, ...
  ...
 ORDER BY CURRENT_TIMESTAMP
OFFSET     10 ROWS       -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows

(I'd rather mark that as a hack - but it's used, e.g. by NHibernate. To use a wisely picked up column as ORDER BY is preferred way)

to answer the question:

--SQL SERVER 2012
SELECT PostId FROM 
        ( SELECT PostId, MAX (Datemade) as LastDate
            from dbForumEntry 
            group by PostId 
        ) SubQueryAlias
 order by LastDate desc
OFFSET 10 ROWS -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows

New key words offset and fetch next (just following SQL standards) were introduced.

But I guess, that you are not using SQL Server 2012, right? In previous version it is a bit (little bit) difficult. Here is comparison and examples for all SQL server versions: here

So, this could work in SQL Server 2008:

-- SQL SERVER 2008
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 10,@End = 20;


;WITH PostCTE AS 
 ( SELECT PostId, MAX (Datemade) as LastDate
   ,ROW_NUMBER() OVER (ORDER BY PostId) AS RowNumber
   from dbForumEntry 
   group by PostId 
 )
SELECT PostId, LastDate
FROM PostCTE
WHERE RowNumber > @Start AND RowNumber <= @End
ORDER BY PostId

Thanks a lot! That's a really good answer! Only question about the sql 2008 one. I want the ORDER BY to happen before the WHERE, as it currently will sort the subset, but we want to select something from the whole set ... Any ideas? :) Once again, thanks
If I do understand you correctly, you'd like to sort by LastDate, right? then we can change the OVER() clause this way: ROW_NUMBER() OVER (ORDER BY MAX(Datemade) desc). And remove last ORDER BY PostId. Now the CTE should be sorted 'sooner' as needed. correct?
Thank you this helped, a note about 2012 sample, order by is mandatory, I was trying this without order by clause and got error "incorrect syntax" had no idea what was wrong until I look into MSDN syntax and learned that order by is mandatory.
Is the first row 1 or 0? Should the WHERE be WHERE RowNumber >= @Start AND RowNumber < @End to get the first 1000 rows if @Start is 0 and @End is 1000?
@CWSpear, well you can try that on your own... but @start = 0 will take the first row and @end = 1000 will mean to take 1000 rows per that page. Check the WHERE clause I used: WHERE RowNumber > @Start AND RowNumber <= @End
F
Felipe V. R.

In order to do this in SQL Server, you must order the query by a column, so you can specify the rows you want.

Example:

select * from table order by [some_column] 
offset 10 rows
FETCH NEXT 10 rows only

And you can't use the "TOP" keyword when doing this.

You can learn more here: https://technet.microsoft.com/pt-br/library/gg699618%28v=sql.110%29.aspx


N
Nicolas Sroczynski
OFFSET     10 ROWS       -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows

use this in the end of your select syntax. =)


T
Tadej

SQL 2008

Radim Köhler's answer works, but here is a shorter version:

select top 20 * from
(
select *,
ROW_NUMBER() OVER (ORDER BY columnid) AS ROW_NUM
from tablename
) x
where ROW_NUM>10

Source: https://forums.asp.net/post/4033909.aspx


S
Sheikh M. Haris

The fix is to modify your EDMX file, using the XML editor, and change the value of ProviderManifestToken from 2012 to 2008. I found that on line 7 in my EDMX file. After saving that change, the paging SQL will be generated using the “old”, SQL Server 2008 compatible syntax.

My apologies for posting an answer on this very old thread. Posting it for the people like me, I solved this issue today.


I don't think this belongs here. The question is regarding a SQL Server query, while you are explaining how to modify a Visual Studio generated file to work with older SQL Server instances.
A
Amin Golmahalle

You can use nested query for pagination as follow:

Paging from 4 Row to 8 Row where CustomerId is primary key.

SELECT Top 5 * FROM Customers
WHERE Country='Germany' AND CustomerId Not in (SELECT Top 3 CustomerID FROM Customers
WHERE Country='Germany' order by city) 
order by city;