Previous and Next Records

[Skemcin]

I am wondering, is there a way to write a SQL statement that will return the records on either side of a single record.For instance, if I have(select) record B in a table, can I write one expression that will return A,B,C - my input being 'B'? I know I could do this with three statements, one for B, one to get the TOP 1 record for anything less than B, and one to get the TOP 1 record for anything greater than B - is there a way to combine this logic - without a stored procedure?Thanks in advance.

[Jerome]

I'm not sure this is the best solution to your problem, but I would relate your records (A,B,C) to an ID field, then construct the query like this :

<%@ Language=VBScript %><%Dim Con, Rst, actualID, previousID, nextIDSet Con = Server.CreateObject("Adodb.Connection")Con.open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("yourBase.mdb") & ";" Set Rst = Server.CreateObject("ADODB.RecordSet")actualID = 2 'this id is related to field that contains your B recordpreviousID = actualID - 1 'here you look for the previous idnextID = actualID + 1 'here you look for the next idSQL = " SELECT records FROM yourBase WHERE id = '" & previousID & "' OR id = '" & actualID & "' OR id = '" & nextID & "' "Rst.Open sql, ConIf Not Rst.EOF Then	Do While Not Rst.EOF  Response.Write(Rst("records"))	Rst.MoveNext	LoopEnd IfRst.CloseSet Rst = NothingCon.CloseSet Con = Nothing%>

Hope this helps,Cheers

[Skemcin]

Hi and thanks for the insight on the issue. I had considered that method but will no doubt run into an issue when the record ids (primary keys) are not in numeric order.If I have A,B,C,D and their ids are 12,13,14,15 - respectively, then when I delete C(14) from the ground, then I will have a problem whenever I load record 13.lol - the lucky number - that was such a coincidence that I picked those numbers - lol.What I have done is write three queries:SELECT myfieldsFROM mytableWHERE id = 13SELECT TOP 1 myfieldsFROM mytableWHERE id > 13SELECT TOP 1 myfieldsFROM mytableWHERE id < 13I was just hoping to find one command to do all that without creating a stored procedure since I am using MySQL.

[Jerome]

Yes, you are right.The only (and fastet) solution would then be a store procedure if you where using sql server...

[Skemcin]

Thanks for the reply. Looks like I'll stick with the three query technique, thanks again.