None of the SQL experts has any suggestions? ;) Mike
-----Original Message----- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: Friday, November 07, 2008 4:36 PM To: cf-talk Subject: Improve SQL for Retrieving The Next Similar Record My goal: I am trying to retrieve the "next" record based on the date from a given record. That part is easy enough. However, part of the criteria is that the "next" record must match as far as nullability. In other words, if the archivedDate in my "known" record IS NULL, then I want to look for the "next" record where archivedDate is also NULL. Conversely, if archivedDate in my "known" record IS NOT NULL, then I want to look for the "next" record where archivedDate is any date value. The SQL below works perfectly, but I'm not convinced it is the best solution. Would anyone have a better solution to this problem that they would be willing to share? SELECT TOP 1 a.alertId FROM Alerts a INNER JOIN AlertParticipants ap ON a.alertId = ap.alertId WHERE ap.colleagueId = '0000094' AND CASE WHEN a.archivedDate IS NULL THEN 'Active' ELSE 'Archived' END = (SELECT CASE WHEN archivedDate IS NULL THEN 'Active' ELSE 'Archived' END FROM Alerts WHERE alertId = 198) AND a.updatedDate < (SELECT updatedDate FROM Alerts WHERE alertId = 198) ORDER BY updatedDate DESC ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314997 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
