4

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.

Community
  • 1
  • 1
jessegavin
  • 74,067
  • 28
  • 136
  • 164
  • Potential duplicate of http://stackoverflow.com/questions/203302/what-is-the-sql-for-next-and-previous-in-a-table – Drew Noakes Mar 21 '11 at 18:22
  • @Drew, you are correct. My question is a duplicate. I am actually really glad you found that! I wasn't able to so I asked the question above. I just voted to close this answer. – jessegavin Mar 21 '11 at 18:29
  • No problem. I'm trying to do the same thing, though via Entity Framework (http://stackoverflow.com/questions/5382023/how-to-select-the-next-and-previous-entity-from-a-table). It's useful to link these questions together for folks to evaluate a range of options. – Drew Noakes Mar 21 '11 at 18:36

4 Answers4

11

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.

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • In the question I stated that I was already sorting the records by a unique PublishDate. I want to know if it is possible to make 1 round-trip to SQL server to select a record by ID as well as the records 'before' and 'after' it when sorted. – jessegavin Jun 09 '10 at 21:46
  • 1
    @jessegavin, you *can* do this in a single trip to the DB if you use a UNION to join two queries with the same columns. – Drew Noakes Mar 21 '11 at 18:21
4

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)
Thomas
  • 63,911
  • 12
  • 95
  • 141
  • I think this example works well for scenarios in which I already have the notion of an 'index' in place (e.g. position 5 of 100). However in my situation, the identifier I want to pass into the query is a guid. - For example if the page url was `abc.com/post.aspx?id={guid}` and then let's say a user got to that page from a Google result, then how would I find the @ItemNumber? – jessegavin May 10 '10 at 19:54
  • @jessegavin - You will *must* impose a sequence/order to have to a notion of previous and next. If you allow a user to hit a page based on some id, you must have a means to determine where in the sequence that item resides and to do that you must impose that sequence yourself using something like ROW_NUMBER(). Google does not allow for this. There is no means from a single link to determine that a given link will appear on page X if the user searches for "Foo". You have to determine that manually as Google simply uses a count of results and a starting number to display its results. – Thomas May 10 '10 at 20:41
  • 1
    Just to clarify, I wasn't suggesting that I would rely on Google to manage a record index or anything. I mentioned Google as a possible referrer from which a user might end up at a particular page in my site. - I guess my problem comes in that I don't want to have to first run a query to determine the @ItemNumber for a given guid so that I can run the second query using the syntax you provided above. - Thank you for your answer though. +1 – jessegavin May 10 '10 at 21:12
1

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
Eelze Wijma
  • 51
  • 2
0

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

Namit
  • 331
  • 3
  • 7