Pagenation on a blog SQL query

I have a blog (non wordpress or anything) written in vb.net. At the moment I pull my data back just by bringing in the last 10 results:

SELECT TOP (@bAmount) bCategory, bID, bImageURL, bInactive, bLargeImage, bPost, bPostDate, bPostTitle, bPoster, bStyle FROM Blog ORDER BY bID DESC

I'm just going to have back and forward buttons to go through the posts, but how do I do the SQL for say post 10-20 last posts rather than the IDs 开发者_如何转开发between the posts. (The IDs are incrimental, but can jump significantly).

So basically when I click back it gives me 10 posts that are 10 older than the first page results?

---

You could use something like this:

DECLARE @rowsPerPage int SET @rowsPerPage = 10    
DECLARE @pageNum int SET @pageNum = 3;     

With SQLPaging    
As   
(      
    Select 
    Top(@rowsPerPage * @pageNum) ROW_NUMBER() OVER (ORDER BY bID) as resultNum,
    bCategory, 
    bID, 
    bImageURL, 
    bInactive, 
    bLargeImage, 
    bPost, 
    bPostDate, 
    bPostTitle, 
    bPoster, 
    bStyle


    FROM Blog    
)    
select * from SQLPaging where resultNum > ((@pageNum - 1) * @rowsPerPage)   

This code should work now but I can't test it against a schema...

---

When you get a chunk of data, store the largest and smallest bID. Then use:

SELECT TOP (@bAmount)
      bCategory, bID
    , bImageURL, bInactive
    , bLargeImage, bPost
    , bPostDate, bPostTitle
    , bPoster, bStyle 
FROM Blog 
WHERE bID < @PreviousMin_bID
ORDER BY bID DESC

or:

SELECT *
FROM
  ( SELECT TOP (@bAmount)
          bCategory, bID
        , bImageURL, bInactive
        , bLargeImage, bPost
        , bPostDate, bPostTitle
        , bPoster, bStyle 
    FROM Blog 
    WHERE bID > @PreviousMax_bID
    ORDER BY bID ASC
  ) tmp
ORDER BY bID DESC

---

Some LIMIT will help you, I guess. Usually you can pass two parameters with LIMIT — start offset and maximum number of rows in result. So, you just need to count the offset for your pages.

Sorry if I'm wrong. It is just pretty common approach for pagination in MySQL. I don't know about MSSQL.

---

New SQL Server version has Offset and Fetch Next keywords in order to offer paging on database site. Here is a good article with sql sample SQL Paging in SQL Server 2012 using SQL ORDER BY OFFSET and FETCH NEXT

declare @currentPage int = 2
declare @rowsPerPage int = 10
select 
    bCategory, bID, bImageURL, bInactive, bLargeImage, bPost, bPostDate, bPostTitle, bPoster, bStyle
from Blog
ORDER BY bID DESC
OFFSET (@currentPage-1)*@rowsPerPage ROWS
FETCH NEXT @rowsPerPage ROWS ONLY