Finding the higest voted Question in last week. (sql query)

Question

Hello

I have a 3 sql tables:

qotwQuestion1a(QuestionId [primarykey], Question, MemberId, PostDate);
qotwVote1a (QuestionId [primarykey], MemberId [primarykey], Vote1a);
qotwMember (MemberId [primarykey], Name, Password, emailId);

I want to write a sql query to find the QuetionId and MemberId of the Question that has the highest vote in the last week. I have written this query in php, but it gives me a wrong result:

$result6 = mysql_query("SELECT MAX(Vote1a) AS highestVote, * FROM qotwMember, qotwQuestion1a , qotwVote1a
     WHERE  qotwMember.MemberId=qotwQuestion1a.MemberId 
     AND  qotwQuestion1a.QuestionId=qotwVote1a.QuestionId 
     AND  qotwQuestion1a.MemberId=qotwVote1a.MemberId
     AND  PostDate>='".$startofweek."' AND PostDate<='".$endofweek."'
     ORDER BY qotwQuestion1a.QuestionId DESC ");
while($row6 = mysql_fetch_array($result6))
  {
  echo "The highest voted question of the last week is: "; echo $row6['highestVote']; echo $row6['MemberId'] . " " . $row6['Name'] . " " . $row6['Password'] . " " . $row6['PostDate'] . " " . $row6['Question']." ".$row6['QuestionId']." ".$row6['Vote1a'];
  echo "<br />";
  }

The $startofweek and $endofweek give the date of the beginning of the last week and end of the last week.

Can someone help me with this, please.

Best Zeeshan

Answer 1

SELECT  *
FROM    (
        SELECT  q.QuestionId, q.MemberID
        FROM    qotwQuestion1a q
        JOIN    qotwVote1a v
        ON      v.QuestionID = q.QuestionID
        WHERE   PostDate BETWEEN $startdate AND $enddate
        GROUP BY
                q.questionID
        ORDER BY
                COUNT(*) DESC
        LIMIT 1
        ) qo
JOIN    qotwMember m
ON      m.MemberID = q.MemberID

Answer 2

Hope you're dealing with ties somewhere ;)

And questions with no answers, for that matter.

Other than that...it looks like you're matching the member to the question, which might not make sense if your tables are set up the way they appear to be.

Answer 3

Well, first of all, using MAX() without groupping is .. useless, you don't need it in this case. Second, if you want your results ordered from highest voted down to lowest voted, why don't you order by Vote1a and just take the first result with a LIMIT clause.

Answer 4

Try this:

SELECT Vote1a AS highestVote, * FROM qotwMember, qotwQuestion1a , qotwVote1a WHERE qotwMember.MemberId=qotwQuestion1a.MemberId AND qotwQuestion1a.QuestionId=qotwVote1a.QuestionId AND qotwQuestion1a.MemberId=qotwVote1a.MemberId AND PostDate>='".$startofweek."' AND PostDate<='".$endofweek."' GROUP BY Votela, * HAVING MAX(Votela) ORDER BY qotwQuestion1a.QuestionId DESC");

Not too sure on the syntax of this one but the basic idea is to have a query that can get all records, and then at the last minute, isolate it to just those records having the MAX(Votela).

I would also recommend not using * unless you absolutely need it. I know it can be a pain if you want most of the columns in the tables, but it is always best practice to only select the columns you need.