Which DB?
"This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -----Original Message----- From: David Gardner To: CF-Talk Sent: Thu Feb 22 20:14:12 2007 Subject: Re: SQL next and previous Add something like this into your where clause WHERE clientID = <cfqueryparam value="#clientID-1#" cfsqltype="cf_sql_integer"/> OR clientID = <cfqueryparam value="#clientID#" cfsqltype="cf_sql_integer"/> OR clientID = <cfqueryparam value="#clientID+1#" cfsqltype="cf_sql_integer"/> and then sort by clientID. Then use array notation to access the parts of the query you want... getClient.clientID[1] will be any client before getClient.clientID[2] will be the selected one getClient.clientID[3] will be any client after I'm sure you get the general idea On 2/14/07, Richard Cooper <[EMAIL PROTECTED]> wrote: > Hi all, > > Im not sure where to begin with coding this one. I want to output buttons for the next and previous row entry > > For example if I had a client table, i could grab the client like this: > > <cfquery name="getClient"> > SELECT clientID > ,fname > ,sname > FROM tblCli > WHERE clientID = <cfqueryparam value="#clientID#" cfsqltype="cf_sql_integer"/> > AND otherfield = 'somevalue' > ORDER BY sname > </cfquery> > > It has a little redundant code in at the moment as clientID would be the PK and return only one entry, but what if I wanted to get the next or previous ID value for the clients in the table? > > I was thinking I'd need to do two left joins but couldn't figure out how to grab the higher/lower entry as defined by the "order by". > > Any ideas how to approach this? > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2. Free Trial http://www.adobe.com/products/coldfusion/flex2/ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:270519 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
