Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Currently I have:

SELECT user.userid, Username, count(answerid) as Answers 
FROM user left outer join answer on user.userid=answer.userid
group by user.userid
order by Answers desc
limit 0,5

This counts the users that have answered the most questions. However I would like to change this to count only the top 5 users who have answered the most questions IN the last 30 days.

The answer table has a field called date1 which contains the date when the answers was posted. Any ideas please? Any help is very much appreciated.

share|improve this question

1 Answer 1

You need a where clause:

SELECT user.userid, Username, count(answerid) as Answers 
FROM user LEFT OUTER JOIN answer ON user.userid=answer.userid
WHERE answer.date1 >= date_sub(curdate(),interval 30 day)
GROUP BY user.userid
ORDER BY Answers desc
LIMIT 0,5

But as others suggested it would be better to query without the outer join - something like this should work:

SELECT user.userid, Username, answer_count.Answers
FROM user,
    (SELECT userid, count(answerid) as Answers FROM answer
     WHERE date1 >= date_sub(curdate(),interval 30 day
     GROUP BY userid) answer_count
WHERE user.userid = answer_count.userid
ORDER BY answer_count.Answers desc
LIMIT 0,5
share|improve this answer
    
This query said "MySQL returned an empty result set (i.e. zero rows)." –  David Nov 25 '10 at 10:41
    
It works inversely but not like this. However I am sure that there are profiles who posted answers in the last 30 days. Still the query returns no results. –  David Nov 25 '10 at 10:48
    
you might select distinct date1 from answer order by date1 desc and check the most recent values –  pacey Nov 25 '10 at 10:49
    
Using the outer join means that the query is far from ideal for the purpose stated ("This counts the users that have answered the most questions") let alone for the purpose the OP is asking about. And it's even more absurd for the last 30 day constraint. –  symcbean Nov 25 '10 at 13:29
    
@pacey: I presume you mean your query will very if there are posts in the alst 30 days to report on - not a solution to the question originall asked? –  symcbean Nov 25 '10 at 13:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.