CODEWITHSUNDEEP
mysqlaggregate-functions
yuyu5
yuyu5

Reputation: 474

MySQL breaks without GROUP BY clause

I'm trying to use MySQL to view metadata. My goal is to view the table that has the highest number of attributes. I have a table (call it T) that holds table_name and num_att (number of attributes) columns.

When I try to run SElECT table_name, MAX(num_att) FROM T; It errors on me that I must have a GROUP BY statement when sql_mode=only_full_group_by which was turned on by default in my installation. So, I turned off only_full_group_by by running SET sql_mode='';. This allowed me to run the above query without using group by statement.

But then, when I run the above query, it outputs the wrong data -- it outputs the correct MAX(num_att) value but combined with the wrong table_name value (it links the MAX(num_att) value with the first table_name value).

e.g.

SELECT table_name, num_att FROM T;

outputs:

+------------+---------+
| table_name | num_att |
+------------+---------+
| customer   | 5       |
| menuitem   | 3       |
| orderdetail| 4       |
| orders     | 7       |
| restaurant | 4       |
+------------+---------+

Then, when I run the aggregate query SElECT table_name, MAX(num_att) FROM T;, I get this

+------------+---------+
| table_name | num_att |
+------------+---------+
| customer   | 7       |
+------------+---------+

when it should be this

+------------+---------+
| table_name | num_att |
+------------+---------+
| orders     | 7       |
+------------+---------+

Does anyone know why this is happening? I am using MySQL version 5.7.14 on Windows 10 WAMP installation without having changed any settings other than the password and the sql_mode (as mentioned above).

For reference, T is the following:

SELECT table_name,count(column_name) AS num_att 
FROM (
    SELECT table_name,column_name,column_type 
    FROM information_schema.columns AS c
        JOIN (
            SELECT table_schema, table_name 
            FROM information_schema.tables 
            WHERE table_schema='cr'
         ) AS t
    USING (table_name);
) AS x
GROUP BY table_name;

where cr is the name of my database.

Upvotes: 1

Views: 518

Answers (2)

Mihai
Mihai

Reputation: 26804

You can use the SUBSTRING_INDEX with GROUP_CONCAT trick

SELECT SUBSTRING_INDEX(GROUP_CONCAT(table_name ORDER BY num_att DESC),',',1), MAX(num_att) FROM T

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

If you need an aggregation function result and the value for this aggregation you can't perform this query avoinding the group by so you must use a subquery (or a join ) eg:

SELECT table_name, num_att
FROM T
where num_att = (select max(num_att) from T)

this features starting from mysql 5.7 .. previous version allow also the use of aggreagated function without group by ...but the use column in group by is correct way ..

Upvotes: 1

Related Questions