SEO

#1 (**permalink**) 03-28-2008, 04:40 AM

Hi,

On Thu, Mar 20, 2008 at 10:35 AM, Edward Corbett <edward@craft-e.com> 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...-group-in-sql/

Baron

#2 (**permalink**) 03-28-2008, 04:40 AM

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: baron.schwartz@gmail.com [mailto:baron.schwartz@gmail.com] On Behalf
Of Baron Schwartz
Sent: 20 March 2008 14:54
To: edward@craft-e.com
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 <edward@craft-e.com> 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...astmax-row-per
-group-in-sql/

Baron

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode