GROUP BY doesn't show all fields-CodePudding

NEW UPDATE: I first sort by a different field called 'fav'

The $result of the $query only shows the field $row['symbol'], it doesn't show the field $row['id']. What am I doing wrong?

    CREATE TABLE `tableName` (
  `id` varchar(15) DEFAULT NULL,
  `symbol` varchar(10) DEFAULT NULL,
  `fav` varchar(15) DEFAULT NULL

)

$query = "SELECT symbol FROM (SELECT * FROM tableName ORDER BY fav DESC ) AS t GROUP BY t.symbol ";

$result = mysqli_query($conn, $query);
if($result->num_rows > 0){
    while($row = mysqli_fetch_assoc($result)){
            echo 'id: '.$row['id'].' symbol: '.$row['symbol'].'<BR>';
    }
}

I'm using PHP 7.4.16

UPDATE: With PHP 8.0.3 I get this ERROR MESSAGE:

Warning: Undefined array key "id"

It's like it doesn't find the the id, however if I run it like this it works fine:

$query = "SELECT * FROM tableName ORDER BY symbol DESC";

CodePudding user response：

I do not understand the question. My best guess is that GROUP BY is not needed:

  SELECT symbol,
         fav,
         id
    FROM tableName
ORDER BY symbol DESC,
         fav DESC,
         id

Another possibility would be to gather all the ids per symbol:

  SELECT symbol,
         GROUP_CONCAT(id SEPARATOR ', ')
    FROM tableName
GROUP BY symbol DESC

Edit to include column fav:

  SELECT symbol,
         fav,
         GROUP_CONCAT(id SEPARATOR ', ') AS ids
    FROM tableName
GROUP BY symbol DESC,
         fav DESC
ORDER BY symbol DESC,
         fav DESC

CodePudding user response：

It is difficult to discern your intent from your SQL query. Your query is this.

SELECT symbol 
  FROM (
      SELECT * FROM tableName ORDER BY symbol DESC 
       ) AS t
 GROUP BY t.symbol

It means the same thing as this. (I omitted the ORDER BY because ORDER BY operations in subqueries aren't guaranteed to carry through to outer queries.)

SELECT DISTINCT symbol FROM tableName

Neither of these queries returns an id column. That's why your php reference to $row['id'] fails.

Is it possible you want ORDER BY rather than GROUP BY? In that case your query would be

SELECT * FROM tableName ORDER BY symbol DESC

Or, is it possible you have duplicate symbol values in your table, and you want the id of just one of the duplicate rows? In that case you want something like this.

SELECT MAX(id) id, symbol
  FROM tableName
 GROUP BY symbol
 ORDER BY symbol DESC

The query finds the largest id value for each distinct value of symbol and returns it.