Do you want the most recent transaction that occurred after the most recent failure? Or do you want a list of the most recent transactions after every failed transaction?
For case number 1 above, you could do something along the lines of: SELECT TOP 1 id, approved, date FROM mytable WHERE date > ( SELECT MAX(date) FROM mytable WHERE approved = 0 ) ORDER BY date DESC If you really mean case number 2, I'll have to put some thought into it. That one one is more tricky. Also, is this data really correct? This would seem to indicate that you are inserting rows at the beginning of the table instead of appending to the end of the table (highest date values are corresponding to lowest id values) and that would mean that every row would get a new id every time a new row is inserted. Is that true? Judah On Fri, Jul 24, 2009 at 1:00 PM, Casey Dougall<[email protected]> wrote: > > Yup... one of those random Friday afternoon stumpers > > If I have a table of transactions like this little thing here. With SQL how > would I grab ID 2 based on the face that ID 3 is 0 > > I need to get the next successful transaction after a failed transaction but > not sure how to go about it. > > ID Approved Date 1 1 -7/6/2009 2 1 -7/5/2009 3 0 -7/4/2009 4 1 > -7/3/2009 5 0 -7/2/2009 6 1 -7/1/2009 > -- > Casey > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:324956 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
