Problem retrieving latest record when using GROUP BY

[Buchead]

Hello....

 

I have a table detailing when members of staff have been on call, going back a couple of years.

 

Table: oncall

 

calloutID

staffID

year

week

 

What I'm attempting to do is pull out the last week they were on call during each year. For some staff it's only once a year, whilst others are multiple times. I'm using the query:

 

SELECT * FROM `oncall` WHERE `staffID` = '5' ORDER BY `year` DESC , `week` DESC

 

When they have only 1 occurrence during a year it's fine, but if there's multiple then it's not pulling out the latest entry for a year.

 

What am I doing wrong?

 

Thanks.

[fenway]

During "each" year? Then you need to group by year and pick the "last" week (with MAX(), I presume).