php - Returning a name from a column using the MAX() function on a different column
Get the solution ↓↓↓
I had a quick question on how to return a certain value from a column after using the MAX() function on a different column. For instance, I am trying to return the full name of the comedian depending on who has the highest number of videos posted on youtube. I am able to get the number of videos posted but I am having trouble returning the name. "countc" is the column keeping track of how many youtube videos a comedian has posted. I am using PHPMyAdmin.
<?php
session_start();
$con = mysqli_connect('localhost','root','') or die("Could not connect");
mysqli_select_db($con, 'youtube') or die(mysqli_error($con));
$output = '';
$query = " SELECT fullname FROM comedian WHERE MAX(countc) AS vidcount ";
$query_result = mysqli_query($con, $query);
if($query_result)
{
while($row = mysqli_fetch_assoc($query_result))
{
$output = "The comedian with the most videos is"." ".$row['vidcount'];
}
echo $output;
}
else{
$output = "Could not find top comedian.";
echo $output;
}
Answer
Solution:
When I get you right you want to order your comedians by the highest number of posted YouTube videos.
Try the following ...
SELECT fullname, countc FROM comedian ORDER BY countc DESC
If you want to limit the result to the number of resultsets you want to have, just append aLIMIT to your query.
SELECT fullname, countc FROM comedian ORDER BY countc DESC LIMIT 1
The above shown example limits the result to a single resultset.
Answer
Solution:
You can use sub query like following :
SELECT fullname FROM comedian WHERE countc = (SELECT MAX(countc) FROM comedian);
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: a non-numeric value encountered
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.