Jump to content

Previous and Next Records


Skemcin
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...