CODEWITHSUNDEEP
mysqlsqlselect
Rahif Abboud
Rahif Abboud

Reputation: 342

MySQL check if MAX value has duplicates

I'm running contests on my website. Every contest could have multiple entries. I want to retrieve if only the MAX value of votes has a duplicate.

The table is as follows:

contest_id    entry_id    votes
1             1           50
1             2           34
1             3           50
2             4           20
2             5           55
3             6           53

I just need the query to show me that contest 1 has a duplicate MAX value without additional information.

I tried this but didn't work:

SELECT MAX(votes) from contest group by contest_id having count(votes) > 1

Upvotes: 2

Views: 1443

Answers (3)

Ilmari Karonen
Ilmari Karonen

Reputation: 50338

You could do it by first selecting the maximum number of votes for each contest ID in a subquery, and then joining against the results (demo on SQLFiddle):

SELECT contest_id, votes
FROM contest
  JOIN (
    SELECT contest_id, MAX(votes) AS votes
    FROM contest GROUP BY contest_id
  ) AS foo USING (contest_id, votes)
GROUP BY contest_id
HAVING COUNT(*) > 1

The nice thing about doing it like this is that it's an independent subquery, so MySQL only needs to rub it once.

Ps. Yes, this is basically identical to JW's answer, but I figured I'd leave it up anyway to show the slightly different syntax I used for the join.

Upvotes: 0

John Woo
John Woo

Reputation: 263723

SELECT  a.contest_ID
FROM    contest a
        INNER JOIN
        (
          SELECT contest_id, MAX(votes) totalVotes
          FROM contest
          GROUP BY contest_id
        ) b ON a.contest_ID = b.contest_ID AND
               a.votes = b.totalvotes
GROUP BY a.contest_ID
HAVING COUNT(*) >= 2

Upvotes: 5

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

This finds the max votes value per contest and counts the entries with that number of votes. It then displays contest with more than one hit.

SELECT contest_id
FROM contests
WHERE votes=(
  SELECT MAX(votes) FROM contests c WHERE c.contest_id=contests.contest_id
)
GROUP BY contest_id
HAVING COUNT(*) > 1;

SQLfiddle for testing.

Upvotes: 5

Related Questions