Robin S. Robin S. - 7 months ago 87
MySQL Question

Order of SQL Query Clauses

I ran into a situation where I don't seem to get what SQL is doing. I have the following table and want to give out all the sorts of coffee which have the most amount of rating=5 with the amount itself.

create table likes
(
CName varchar(30),
UName varchar(30),
Rating int
);

insert into likes (CName, UName, Rating)
values ('Java', 'Klaus', '5'),
('Super', 'Klaus', '5'),
('MP', 'Klaus', '3'),
('Java', 'Marc', '5'),
('Mp', 'Marc', '5'),
('Super', 'Marc', '2'),
('Java', 'Nine', '2'),
('Super', 'Nine', '0'),
('MP', 'Karo', '3'),
('Super', 'Fabian', '4');


However this solution doesn't work as intended

SELECT
favcof.CName, favcof.cnt
FROM
(SELECT l.CName, COUNT(CName) cnt
FROM likes l
WHERE l.rating = 5
GROUP BY CName) favcof
WHERE
favcof.cnt = (SELECT MAX(favcof.cnt))


It executes as if there is no outer where-clause and gives out all sorts of coffees with their amount of rating = 5.

Answer Source

The expression (select max(favcof.cnt)) doesn't do anything. You can just drop the select and you will get favcof.cnt = favcof.cnt.

This is a little complicated, because favcof.cnt = max(favcof.cnt) would generate a syntax error because aggregation functions are not allowed in the where clause. So, the select subquery is actually an aggregation subquery with no from. Because there is only one value, it returns that value.

You want a correlated subquery. This would look like:

SELECT favcof.CName, favcof.cnt
FROM (SELECT l.UName, count(UName) as cnt
      FROM likes l
      WHERE l.rating=5
      GROUP BY UName
     ) favcof
WHERE favcof.cnt = (SELECT MAX(favcof2.cnt)
                    FROM (SELECT l2.UName, count(l2.UName) as cnt
                          FROM likes l2
                          WHERE l2.rating=5
                          GROUP BY l2.UName
                         ) favcof2
                   );

There are definitely other ways to write this query. However, this should help you understand why your version does not do what you want it to do.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download