CODEWITHSUNDEEP
mysqlsqlperlcgi
Pedro
Pedro

Reputation: 1450

How to select users with most votes, from a database?

I have a small system that accepts votes, on my query I'm selecting the vote(name of user) and the total of votes for this user. My Idea is to only display the user with more votes, with the query that i have I know that i can add ORDER BY and get the first value which will be the one with the highest umber of votes. But i'm trying to identifie if two persons have the number of votes. any ideas how can i do that?

I'm using cgi.

my $connection = $MY_CONNECTION->prepare("SELECT voto, COUNT(*) total FROM votos  WHERE mes = 12 GROUP BY vote HAVING COUNT(*) > 0 ORDER BY COUNT(*)");
$connection->execute || print "ERROR 1";

my @resultados;
my $info = '';


while ($info = $connection->fetchrow_hashref()) {

    my $nombre   = $info->{voto};
    my $totalVotos = $info->{total};

my $winner = "";
my $temp2  = "";
my $temp   = $totalVotos ;

if ($temp2 => $temp) {
    $temp2 = $totalVotos ;
    $winner = $nombre   ; 

}


    print "<p><strong>Winner: </strong> $winner </p> "; 
    print "<hr>";
}

Upvotes: 1

Views: 184

Answers (3)

jhnc
jhnc

Reputation: 16662

You can return everyone who has the highest number of votes (and how many votes) with:

select voto, count(*) as total from votos
    where mes = 12
    group by voto -- I assume "vote" in your question was typo
    having total = (
        select max(ct) from (
            select voto,count(*) as ct from votos group by voto
        )
    )
    and total > 0 -- from your original but not sure it can fail
;

You may also be able to use a rank() function.

Without modifying your SQL, you could change the perl to something like:

# ...

# initialisation shouldn't be inside while loop
my $winner = "";
my $temp2  = "";

while ($info = $connection->fetchrow_hashref()) {
    my $nombre = $info->{voto};
    my $totalVotos = $info->{total};

    if ($temp2 < $totalVotos) { # your original "=>" seems wrong
        $temp2 = $totalVotos;
        @winner = $nombre;
    elsif ($temp2 == $totalVotos) {
        push @winner, $nombre;
    }
}

# postprocessing shouldn't be inside while loop
$winner = join(", ", @winner); # or whatever
print "<p><strong>Winner: </strong> $winner </p> "; 
print "<hr>";

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I would recommend rank():

SELECT voto, total
FROM (SELECT voto, COUNT(*) as total,
             RANK() OVER (ORDER BY COUNT(*) DESC) as seqnum
      FROM votos v
      WHERE mes = 12
      GROUP BY voto
     ) v
WHERE seqnum = 1;

Note that HAVING COUNT(*) > 0 does nothing. In your query, it is not possible for COUNT(*) to be equal to or less than 0.

Upvotes: 1

Ed Bangga
Ed Bangga

Reputation: 13006

use subquery then apply order by, to limit the result by 2 add limit 2.

select t1.voto, t1.ct as total from (
    SELECT voto, count(1) ct FROM votos  
    WHERE mes = 12 
    GROUP BY voto
    HAVING COUNT(1) > 1) t1 
order by t1.total desc limit 2

Upvotes: 1

Related Questions