getting next and previous id

Question

getting next and previous id

bladerunner148

SSCommitted

Points: 1824
More actions
December 8, 2008 at 7:48 am

#196886

I have a table called Album. This table has a pic_id (int) and album_type_id (int) and small_thumbnail, large_thumbnail.
Right now, this sql statement returns the previous id.
Select A.pic_id, A.Album_Type_Id,(Select Top 1 B.pic_id From Album B Where B.pic_id = A.pic_id - 1 order By B.pic_id) as Next_Id From ALbum A Where A.pic_id = @pic_id And A.Album_Type_Id = @Album_Type_Id
This works like a charm when the pic_id is in sequential order. But this fails when say the table has the data like this.
pic_id album_type_id
1 1
5 1
9 1
If the user is at pic_id = 5, they can't go to the previous one since A.pic_id - 1 = 4, there is no this id in the table. Is there a way to tweak this statement so it grabs the previous id? Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

You can use an APPLY to do this pretty easily, but the performance will not be great because it becomes RBAR because of the correlated sub-query. If the data set is not that big, something like this will work:

[font="Courier New"]CREATE TABLE #tmp (Pic_ID INT, Album_Type_ID INT)

INSERT #tmp VALUES (1,1)

INSERT #tmp VALUES (5,1)

INSERT #tmp VALUES (9,1)

SELECT

*

FROM

#tmp T

OUTER APPLY (SELECT TOP 1 X.Pic_ID, X.Album_Type_ID

FROM #tmp X

WHERE X.Pic_ID < T.Pic_ID

ORDER BY Pic_ID DESC) A[/font]

If you are lucky, Jeff or Gail may post with a solution that will be faster.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Select A.pic_id, A.Album_Type_Id, (Select MAX(B.pic_id) From Album B Where B.pic_id < A.pic_id) as Next_Id

From ALbum A

Where A.pic_id = @pic_id

And A.Album_Type_Id = @Album_Type_Id

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bladerunner148 SSCommitted Points: 1824 More actions · Answer 3

bladerunner148

SSCommitted

Points: 1824

December 8, 2008 at 8:09 am

#909158

Thanks SSCrazy..it worked pretty great.

bladerunner148 SSCommitted Points: 1824 More actions · Answer 4

Thanks Chris! I just added one more parameter on the subquery (B.Album_Type_ID).

Select A.pic_id, A.Album_Type_Id,

(Select MAX(B.pic_id) From Album B Where B.pic_id < A.pic_id And B.Album_Type_Id = @Album_Type_Id) as Next_Id

From ALbum A

Where A.pic_id = @pic_id

And A.Album_Type_Id = @Album_Type_Id

Thank you both!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Thanks for the feedback!

As a matter of interest, did you run both methods? Did you measure / notice a difference in performance?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bladerunner148 SSCommitted Points: 1824 More actions · Answer 6

Currently, my table is not that huge, so it probably took the same time to get the results. I can try in a larger table and let you guys know the time difference. Thanks for the help.