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

Question

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

It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Apr 09 '20 at 14:15

Marcel · Accepted Answer · 2020-04-09T06:50:35.503

1

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.

edited Apr 09 '20 at 06:50

answered Apr 09 '20 at 06:43

Marcel

4,854
1
14
24

really? check again. – Marcel Apr 09 '20 at 06:46
should i change it to row['fullname'] in the output statement as well – Hafsa Apr 09 '20 at 06:50
Okay just tried this and its giving me a name but its giving me the comedian with the lowest number of videos instead – Hafsa Apr 09 '20 at 06:52
Okay I just changed it to ASC and it seems to be working – Hafsa Apr 09 '20 at 06:54
Hey Hafsa, you can access everything you selected in your sql statement with `$row['columnname']`. Its important, that the column you want to output was mentionend in your `SELECT` statement. If you 're unsure what 's inside your `$row` array output it with `var_dump($row)`. – Marcel Apr 09 '20 at 06:54

score 1 · Answer 2 · answered Apr 09 '20 at 06:58

1

You can use sub query like following :

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

answered Apr 09 '20 at 06:58

Forge Web Design

815
4
8

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

2 Answers2