> hi, i have the following query that retrieves the latest quiz date and > quiz percent > > the problem is that if the student takes the same test back to back > right away somteimes it can throw an error from the sub query > > is there a way of restructuring the query so that i can select the > info without it triggering the error? > Not that this helps you with your question, but have you considered perhaps restructuring your database?
I would suggest something along the lines of... Current_Test_Results 1 test per customer_id, stores the most resent quiz Historical_Test_Results many tests per customer_id, stores all test results. Only do update/insert/deletes into Current_Test_Results and write a trigger on that table that will add/delete rows in your Historical_Test_Results. Alternatively, some folks like to just have the history table and a trigger that updates a pointer in some other table that points to the most recent quiz_id. Just food for thought. Any time I start doing complicated sub-queries to get the most recent entry I tend to think I need to rethink my data instead of my query. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:300632 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
