OK, here's a query I wrote without actually having the data and structures,
so I hope it works. I made two assumptions:
1) The unique ID for a test result was the "TestID" column, change that to
whatever the actual column name is
2) TestID is an incremental ID column, the most recent one has the largest
ID value
SELECT tests1.QuizDate
, tests1.QuizID
, tests1.QuizPercent
FROM Test_Board_Acupuncture tests1
INNER JOIN (
SELECT MAX(TestID) AS TestID
, QuizID
FROM Test_Board_Acupuncture
WHERE Customer_ID = #COOKIE.TCMTests#
GROUP BY QuizID ) tests2
ON tests1.TestID = tests2.TestID
On Thu, Mar 6, 2008 at 9:29 AM, Jeff Price <[EMAIL PROTECTED]> wrote:
> > 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:300634
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
