Wednesday, March 21, 2012

getting row before in table

Im using following code to get a row from database, but how do I get the row before this row?

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE getMessage

@.username varchar(24),

@.ID int

AS

BEGIN

SELECT Message

FROM Messages

WHERE Username = @.username AND ID = @.ID

END

GO

And another question how do you get the row after the row?

Thank you.

Paul.

SELECT

Message

,(SELECT TOP 1 Message FROM dbo.Messages M1 WHERE M1.ID < M.ID ORDER BY ID ASC) [priorRecord]

,(SELECT TOP 1 Message FROM dbo.Messages M1 WHERE M1.ID > M.ID ORDER BY ID ASC) [nextRecord]

FROM dbo.Messages M
WHERE UserName = @.UserName AND ID = @.ID

|||

Thank you.

Thats was ment to be 2 separate questions, i guess i just have to delete one of the lines i dont need.

|||

Sorry, I thought you were looking for "bookend" records for the passed ID. I also noticed an error in the subquery returning priorRecord. It should be ordered DESC instead of ASC.

So which answer did I not help you with? ;)

No comments:

Post a Comment