That's an excellent way to do it, just be sure to order by in the second clause as well, as the query is returned in the order it is listed in the db.
TOP 1 ContentID FROM tblContent WHERE ContentID > 4 ORDER BY ContentID - Matt Small -----Original Message----- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: Monday, February 28, 2005 11:24 AM To: CF-Talk Subject: RE: Simple way to get row before and after Oppps, you'll need an ORDER BY clause in there too... SELECT ContentID, (SELECT TOP 1 ContentID FROM tblContent WHERE ContentID < 4 ORDER BY ContentID DESC) 'Before', (SELECT TOP 1 ContentID FROM tblContent WHERE ContentID > 4) 'After' FROM tblContent WHERE ContentID = 4 >From a table I'm working on at the moment. Ade -----Original Message----- From: Adrian Lynch [mailto:[EMAIL PROTECTED] Sent: 28 February 2005 16:15 To: CF-Talk Subject: RE: Simple way to get row before and after Of the top of my head... SELECT ID, (SELECT TOP 1 ID FROM YourTable WHERE ID < 4) 'Before', (SELECT TOP 1 ID FROM YourTable WHERE ID > 4) 'After' FROM YourTable WHERE ID = 4 Ade -----Original Message----- From: gabriel l smallman [mailto:[EMAIL PROTECTED] Sent: 28 February 2005 16:10 To: CF-Talk Subject: Simple way to get row before and after Im trying to obtain the IDs of the rows before and after say ID 4. So if I have 4, I need to find 1 and 7 (note row ids will not be sequential) I would prefer not to pull entire list, and loop to find id 4 and grab the row # before and after. If db gets big this could get slow. ID CATID Title 1 8 hello moto 4 8 this is good 7 8 can you dig it 14 8 try again Anyone have an idea that might be more efficient? Thanks gabe ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:196795 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
