Limit each group in a group

Question

Now I understand that this was asked several times earlier, but I tried to apply various existing solutions to my specific problems for quite some time without success. So I’m moving here hoping for some guidance.

I have a table called tblanswers that contains answers related to various questions in another table. I want to get a counter for each answer for a specific question id, but limit it to the first first answer every month.

Sample data from tblanswers:

id qid answer timestamp 72 162 2 1366027324 71 161 4 1343599200 70 162 2 1366014201 69 161 4 1366011700 68 162 2 1366006729 67 161 3 1366010948 66 162 2 1365951084 

This is the query that I still have:

 SELECT *, COUNT(*) c FROM( SELECT answer, timestamp, YEAR(FROM_UNIXTIME(timestamp)) yr, MONTH(FROM_UNIXTIME(timestamp)) mo FROM tblanswers WHERE qid = 161 ORDER BY timestamp ASC ) q GROUP BY YEAR(FROM_UNIXTIME(timestamp)), MONTH(FROM_UNIXTIME(timestamp)), answer 

This would give me something like this: (dates and numbers in the data samples are not accurate)

 answer yr mo c 1 2013 5 5 2 2013 5 3 3 2013 5 2 1 2013 6 5 2 2013 6 15 3 2013 6 7 

Let's say I want to see only the first three answers per month, and then the number can never be more than 3. How can I limit every month?

The final data should be the sum of each response, for example:

 answer num_answers 1 2 2 3 3 3 

I think one of these solutions may work, but not like that: http://code.openark.org/blog/mysql/sql-selecting-top-n-records-per-group http: //code.openark. org / blog / mysql / sql-selecting-top-n-records-per-group-another-solution

Any help is appreciated. Thanks!

Answer 1

This solution is based on the top-N-per-group method here

 SELECT answer, COUNT(*) num_answers FROM (SELECT answer, yearmonth, @rn := CASE WHEN @prevmonth = yearmonth THEN @rn + 1 ELSE 1 END rn, @prevmonth := yearmonth FROM (SELECT @rn := NULL, @prevmonth := NULL) init, (SELECT answer, YEAR(FROM_UNIXTIME(timestamp))*100+MONTH(FROM_UNIXTIME(timestamp)) yearmonth FROM tblanswers WHERE qid = 220 ORDER BY timestamp) x) y WHERE rn <= 3 GROUP BY answer 

SQLFIDDLE

Answer 2

How about this solution:

 SELECT qid, answer, YEAR(FROM_UNIXTIME(timestamp)) yr, MONTH(FROM_UNIXTIME(timestamp)) mo, COUNT(*) no FROM tblanswers WHERE qid = 161 GROUP BY answer, yr, mo HAVING COUNT(*) <= 2 ORDER BY timestamp ASC; 

and fiddle: http://sqlfiddle.com/#!2/1541eb/126

Answer 3

There is no reason to reinvent the wheel , and you run the risk of having a buggy, suboptimal code. Your problem is the trivial distribution of a common for each problem with the limit of groups (see also the tag limit-per-group ). There are already proven and optimized solutions to solve this problem .