CODEWITHSUNDEEP
phpmysql
Hafsa
Hafsa

Reputation: 23

Returning a name from a column using the MAX() function on a different column

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;
    }

Upvotes: 0

Views: 42

Answers (2)

Forge Web Design
Forge Web Design

Reputation: 835

You can use sub query like following :

SELECT fullname FROM comedian WHERE countc = (SELECT MAX(countc) FROM comedian);

Upvotes: 1

Marcel
Marcel

Reputation: 5119

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 a LIMIT 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.

Upvotes: 1

Related Questions