CODEWITHSUNDEEP
phpmysql
Emad Ha
Emad Ha

Reputation: 1222

mysql get max value with multiple choices

i have this poll thing, Poll A, has 3 options let's say: Option a option b option c, option a got 3 votes, option b got 2 votes option c got 3 votes, 

    OPTIONS     VOTES 
    option a       3
    option b       2
    option c       3

and i have this mysql query which gets the options and orders by votesCount, limit 1 to get the top answer, but in my example, there are two options on top, they both have the highest values, i want a query to get those two options, n not only one, so i have to get rid of LIMIT 1

Mysql query is 

$query = "SELECT `option` FROM `options` WHERE `pid` = '$pid' AND `votesCount` != '0' ORDER BY `votesCount` DESC LIMIT 1";

any suggestion?

Upvotes: 0

Views: 213

Answers (2)

Emad Ha
Emad Ha

Reputation: 1222

Thanks to @Gordon Linoff for the hint, this is how it is now

$query = "SELECT `option` FROM `options` WHERE `pid` = '$pid' AND `votesCount` = (SELECT MAX(`votesCount`) FROM `options` WHERE `pid` = '$pid' ORDER BY `votesCount` DESC LIMIT 1)";
    ///Just a DB function, don't mind the 0, i'm using a class
    $res = $db->get_rows($db->select($query),0);
    $merged = array();
    foreach ($res as $r){
        $merged[] = $r->option;
    }
    $merged = implode(',',$merged);
    return $merged;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Here is a standard way in any SQL dialect:

select p.*
from poll p
where p.votes = (select max(votes) from poll)

Upvotes: 4

Related Questions