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

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. alt text

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.

alt text

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.

sqlsql-serverpagination


Solution 1:[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.

Solution 2:[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)

Solution 3:[3]

Based on the answer of Thomas you could do the following.The @Id is the GUID you have a unique post.

With OrderedPosts As
    (
    Select Id
        , Title
        , PublishDate
        , ROW_NUMBER() OVER ( ORDER BY PublishDate ) As ItemRank
    From Posts
    )
Select top 3 *
From OrderedPosts
Where ItemRank > (select ItemRank from OrderedPosts where Id = @Id) - 2

Solution 4:[4]

Refer following article at microsoft tech net

http://social.technet.microsoft.com/wiki/contents/articles/23811.paging-a-query-with-sql-server.aspx

Hope this help

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 TomTom
Solution 2 Thomas
Solution 3 Eelze Wijma
Solution 4 Namit

Related Questions