I think you'll want an order by on those: to get the previous:
SELECT TOP 1 idNumber FROM Table WHERE idNumber < #myNumber# ORDER BY idNumber DESC or to get the next: SELECT TOP 1 idNumber FROM Table WHERE idNumber > #myNumber# ORDER BY idNumber ASC ~Brad -----Original Message----- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 15, 2008 1:23 PM To: CF-Talk Subject: RE: SQL Question, get previous record If you are using SQL Server, you can use something like this to get the previous: SELECT TOP 1 idNumber FROM Table WHERE idNumber < #myNumber# or to get the next: SELECT TOP 1 idNumber FROM Table WHERE idNumber > #myNumber# M!ke -----Original Message----- From: Scott Stewart [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 15, 2008 1:21 PM To: CF-Talk Subject: SQL Question, get previous record I have a basic table It has a numeric primary key and a field that determines display order. The records are displayed based on the record order. However the record order may not be continuous (IE: 1, 2, 5, 7, 10) The application calls for an up/down order change feature. What I need to be able to do is select record order 7 and move it up or down So I'd need to be able to get the order number prior to or the next one. Any Ideas on how to retrieve this? sas -- Scott Stewart ColdFusion Developer ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296626 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
