Is this query correct? Something going wrong with getting MAX value.

[imgrooot]

I have a table where I would like to find the maximum value from a certain column and retrieve a single result. But it doesn't seem to work properly.

I don't get any errors. The issue is that it's retrieving the entry_id from one row and the votes from another row. But I would like the row with max value to retrieve the same row's entry_id.

Here is my code. Can you see what's wrong with it?

$find_entries = $db->prepare("SELECT entry_id, MAX(votes) as vts FROM entries WHERE votes > :votes");
$find_entries->bindValue(':votes', 0);
$find_entries->execute();
$result_entries = $find_entries->fetchAll(PDO::FETCH_ASSOC);
if(count($result_entries) > 0) {
  foreach($result_entries as $row) {
    $entry_id  =	$row['entry_id'];
    $votes     =	$row['vts'];

    ?>
    <div>
      <?php echo $entry_id; ?>
    </div>
    <div>
      <?php echo $votes; ?>
    </div>
    <?php

  }
}

 

[requinix]

If you ever need functions like MAX or SUM or COUNT and you don't want to count the entire table at once then you need a GROUP BY clause.

If you want to select a column and then use those functions on others, you should probably be GROUPing BY that first column.

[Barand]

Perhaps you want to do something like this

SELECT entry_id
     , votes
FROM entries
WHERE votes = ( SELECT MAX(votes) FROM entries );

 

[imgrooot]

7 hours ago, Barand said:

Perhaps you want to do something like this

SELECT entry_id
     , votes
FROM entries
WHERE votes = ( SELECT MAX(votes) FROM entries );

 

Perfect! That's exactly it. 

Here is my updated query with your fix.

$find_entries = $db->prepare("SELECT entry_id, votes FROM entries WHERE votes = (SELECT MAX(votes) FROM entries)");
$find_entries->execute();
$result_entries = $find_entries->fetchAll(PDO::FETCH_ASSOC);
if(count($result_entries) > 0) {
  foreach($result_entries as $row) {
    $entry_id  =	$row['entry_id'];
    $votes     =	$row['votes'];

    ?>
    <div>
      <?php echo $entry_id; ?>
    </div>
    <div>
      <?php echo $votes; ?>
    </div>
    <?php

  }
}

 

[Barand]

No need to prepare/execute a query with no input parameters. Just use

$find_entries = $db->query("SELECT entry_id, votes FROM entries WHERE votes = (SELECT MAX(votes) FROM entries)");

 

[imgrooot]

1 hour ago, Barand said:

No need to prepare/execute a query with no input parameters. Just use

$find_entries = $db->query("SELECT entry_id, votes FROM entries WHERE votes = (SELECT MAX(votes) FROM entries)");

 

So then it would be like this? But If I am binding parameters, then I use the prepare/execute method yes?

$find_entries = $db->query("SELECT entry_id, votes FROM entries WHERE votes = (SELECT MAX(votes) FROM entries)");
$result_entries = $find_entries->fetchAll(PDO::FETCH_ASSOC);
if(count($result_entries) > 0) {
  foreach($result_entries as $row) {
    $entry_id  =	$row['entry_id'];
    $votes     =	$row['votes'];

    ?>
    <div>
      <?php echo $entry_id; ?>
    </div>
    <div>
      <?php echo $votes; ?>
    </div>
    <?php

  }
}

 

[Barand]

Yes

[imgrooot]

3 hours ago, Barand said:

Yes

Good to know.

[taquitosensei]

Just curious would this be any more efficient? 

SELECT entry_id, votes FROM entries group by entry_id order by votes ASC

 

[Barand]

By "more efficient" do you mean returning all the ids instead of just the one you are interested in?

[taquitosensei]

Yeah ok nm I wasn't thinking when I wrote that. 

[Barand]

You could find the one with the highest votes with ...

SELECT entry_id, votes FROM entries order by votes DESC LIMIT 1;

... but you wouldn't know if there were two or more with the same highest number. Sod's Law dictates that if you have LIMIT N then there will be N+1 tying in the top position.