Tom Tom - 2 months ago 9
SQL Question

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?

Answer

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