How to select 'previous' and 'next' records in SQL?

Question

I am building a blog-post detail page on my site that will display display a 'previous' and 'next' link similar to how a typepad blog post works. See example below.

I am wondering if there is a way to query the database which accomplishes something like the image below where it selects the 'current' record (in yellow) for display, but also selects the next and previous records (in green) when sorted by PublishDate.

Also, for clarification, the database table I am working with has unique publish dates. Sorry that the example image shows multiple records with the same PublishDate.

Answer 1

You dont. SQL has no previous, next concept. YOu basically ask for the next top 1 in an ordered query (by date for example) after the one you already have. THis means you need to have / impost an order (by order by statement) for filtering.

Answer 2

You need a means of ordering the posts. If you have that, then you could do something like the following in SQL Server 2005+ where you pass in the item number you want:

With OrderedPosts As
    (
    Select ...
        , ROW_NUMBER() OVER ( ORDER BY PublishDate ) As ItemRank
    From ..
    Where ...
    )
Select
From OrderedPosts
Where ItemRank = @ItemNumber

In the site code, you would need to keep track of what number you were currently on and then subtract one and requery to get the previous or add one and requery to get next.

If you wanted the next and previous along with the current in a single query, then you could do something like:

With OrderedPosts As
    (
    Select ...
        , ROW_NUMBER() OVER ( ORDER BY PublishDate ) As ItemRank
    From ..
    Where ...
    )
Select
From OrderedPosts
Where ItemRank Between (@ItemNumber - 1) And (@ItemNumber + 1)