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
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');
(SELECT l.CName, COUNT(CName) cnt
FROM likes l
WHERE l.rating = 5
GROUP BY CName) favcof
favcof.cnt = (SELECT MAX(favcof.cnt))
(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.