Great article. Got me on the right track.
Actual solution I went with was something like:
select
`vPamLearner`.`learnerId`,
`vPamLearner`.`usedName`,
`vPamLearner`.`surname`,
max(prComp.dateSubmitted) latestSubmitted,
max(prComp.FProgressReviewId) latestSubmittedId,
max(prStarted.dateStarted) latestStarted,
max(prStarted.FProgressReviewId) latestStartedId
from vPamLearner
left join vPamFProgressReview prComp on vPamLearner.learnerId =
prComp.learnerId and prComp.status = 'c'
left join vPamFProgressReview prStarted on vPamLearner.learnerId =
prStarted.learnerId and prStarted.status = 's'
where
`vPamLearner`.`assessorId` = 5
group by `vPamLearner`.`learnerId`;
Cheers
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Baron Schwartz
Sent: 20 March 2008 14:54
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: Selecting the most recent entry for each object.
Hi,
On Thu, Mar 20, 2008 at 10:35 AM, Edward Corbett <[EMAIL PROTECTED]> wrote:
> Hi,
>
>
>
> I have a load of reviews in a table and, for each person I want to pull
out
> the most recent review.
>
>
>
> So, if I wanted to do this for a single person I would order by date desc
> and use "limit 1". But I'm not sure how to do this in bulk.
Try the techniques I wrote about here:
http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per
-group-in-sql/
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
