how select from the database where the colum has the highest value in a table in sql-CodePudding

i have this table that has some data, but i want to get the row where a paticular coluom votecount has the highest value:

id	votecount
1	0
2	1
3	1
4	13

i tried this sql statement:

$selectr = "SELECT *, MAX(`votecount`) from `audio`"; 
$stmt = $conn->prepare($selectr);
$stmt->execute([]);
while($row = $stmt->fetch())
{
    $userid = $row["id"];
    $votecount = $row["votecount"];
    echo $userid;
    echo $votecount;
}

but it echos out 10 which means it got the first item in the table and the value is 0, which is wrong, its not getting the highest column

so how do i fix this

CodePudding user response：

Try this:

$selectr = "SELECT * from `audio` where `votecount` = ( SELECT MAX(`votecount`) from `audio`)"; 
$stmt = $conn->prepare($selectr);
$stmt->execute([]);
while($row = $stmt->fetch())
{
    $userid = $row["id"];
    $votecount = $row["votecount"];
    echo $userid;
    echo $votecount;
}

CodePudding user response：

Try correct: $userid = $row["userId"]; to $userid = $row["id"];