Jump to content
imgrooot

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

Recommended Posts

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

  }
}

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Perhaps you want to do something like this

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

 

Share this post


Link to post
Share on other sites
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

  }
}

 

Share this post


Link to post
Share on other sites

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)");

 

Share this post


Link to post
Share on other sites
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

  }
}

 

Share this post


Link to post
Share on other sites

Just curious would this be any more efficient? 

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

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.