Problem using LIMIT on groups of data

[Buchead]

Hello all,

 

I have 1 table I'm having trouble pulling the required data from in 1 query, and simply wonder if it's not possible and should be split into 2 part:

 

table:

userTasks

 

fields:

taskID

userID

year

taskNumber

 

What I'm attempting to do is pull out the top 5 tasks for each user in a given year. As the table will contain multiple year's data (the taskNumber restarts at 1 for each year), is it possible to pull out the top 5 from each year in one query?

 

I'd thought of:

 

SELECT * FROM `userTasks` WHERE `userID`='1' ORDER BY `year` ASC, `taskNumber` ASC LIMIT 5

 

but that, as expected, only pulled out the very 5 top records. Adding in a GROUP BY `year` command didn't work.

 

 

A solution I have working is to 1st query the table and pull out all the individual `year` numbers and then cycle through that array pulling out the data. Just wondering if I could save the trouble and use only 1 query.

 

Thanks for any assistance,

 

Clive.

[fenway]

The problem is that you can't group by in the "middle" of a join... or LIMIT a join.

 

One way to do this would be to use user variable to assign a value of 1 to the first 5 from each year, and then a 0 for the remaining ones -- and then filter with HAVING magicField = 1.

[aschk]

It is possible using a COUNT, let me get to it.

[aschk]

Here is a preliminary :

 

SELECT ut1.userID
      ,ut1.taskNumber
      ,ut1.`year`
      ,COUNT(*) as 'count'
FROM userTasks ut1
JOIN userTasks ut2 ON ut1.userID = ut2.userID AND ut1.`year` < ut2.`year`
GROUP BY ut1.userID, ut1.`year`, ut1.taskNumber
HAVING `count` <= 5
ORDER BY userID DESC, `year` DESC