query to find out the number of first answers by a user in a Q&A website using MySQL

I am having a problem in writing the query for this. I have a q&a website where I want to find out the number of first answers by a user - for all the questions answered by a user, which answers were the first for the particular question.

I have two tables -

• questions - id, question
• answers - id, question_id, answering_userid, date

I have broken this into two steps

1. find out the answers by a user
   • SELECT id FROM answers WHERE answering_userid = "ABC"
2. find out if they are the first
   • ????? This is where I am having problem. I have figured that it will employ MIN(date).

Can somebody help me 开发者_C百科in implementing this?

Regards

You probably want something like this:

select a.id, a.answering_userid
from answers a
inner join (
   select answering_userid, question_id, min(date) as mindate
   from answers
   group by answering_userid, question_id
) first on a.question_id = first.question_id 
    and a.date = first.mindate
    and a.answering_userid = first.answering_userid

Note that this will return dupes if you have 2 answers at the exact same moment. Also, this is completely untested.

With the help of another user codefly I have got the answer I was looking for.

select a.id, a.answering_userid
from answers a
inner join (
   select answering_userid, question_id, min(date) as mindate
   from answers
   group by question_id
) first on a.question_id = first.question_id 
    and a.date = first.mindate
    and a.answering_userid = first.answering_userid
where a.answering_userid = "ABC"