Jump to content

Previous and Next Records


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 post
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 post
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 post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...