Implement swap (skip / take) with this request

Question

I tried to understand a little how to implement custom paging in SQL, for example, to read articles like this .

I have the following query that works fine. But I would like to implement paging with this.

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

What I want

I have forum posts with related posts. I want to receive messages with the latest posts added, so I can select the recently discussed posts.

Now I want to get the "top 10 to the last 20 active messages" instead of the "top 10".

What i tried

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

Any ideas how to implement it?

Answer 1

SQL Server 2012 is 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 would rather note this as a hack, but it was used, for example, by NHibernate. Using a reasonably matched column as ORDER BY is the 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 

The new offset and fetch next keywords were introduced (only the following SQL standards).

But I assume that you are not using SQL Server 2012 , right? In the previous version, this is a bit (a bit) complicated. Here is a comparison and examples for all versions of SQL Server: here

So this may 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 

Answer 2

To do this in SQL Server, you must order the query in a column so that you can specify the rows you want.

Example:

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

Nor can you use the TOP keyword.

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

Answer 3

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

Answer 4

 OFFSET 10 ROWS -- skip 10 rows FETCH NEXT 10 ROWS ONLY; -- take 10 rows 

use this at the end of the selection syntax. =)