On Wed, May 24, 2006 at 04:58:46PM +1000, Dale Fraser wrote:
> Have a table with the following field
>
>
>
> subjectCode
>
> subjectHand
>
> subjectSex
>
> testedDate
>
> sessionName
>
>
>
> I want to get a query which returns the three subject fields, 1 record per
> subject code returning the most recent record based on testedDate and the
> session that was selected in that date.
The following assumes that (subjectCode, testedDate) is a candidate key
(ie. you can't have more than one test for the same subjectCode on the
same date). You might need to play around with it more if that ain't
the case, in particular you'll likely need to add/make use of a
generated primary key id field.
You get the subjectCode and most recent testedDate with something like:
SELECT subjectCode, MAX(testedDate) AS testedDate
FROM SomeTable
GROUP BY subjectCode
Some subquery magic lets you use this a-la:
SELECT SomeTable.*
FROM SomeTable INNER JOIN (
SELECT subjectCode, MAX(testedDate) AS testedDate
FROM SomeTable
GROUP BY subjectCode
) DerivedTable
ON SomeTable.subjectCode = DerivedTable.subjectCode
AND SomeTable.testedDate = DerivedTable.testedDate
Untested and unwarranted. ;)
-T
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"cfaussie" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at http://groups.google.com/group/cfaussie
-~----------~----~----~----~------~----~------~--~---
