I have this very simple table:
CREATE TABLE MyTable
(
Id INT(6) PRIMARY KEY,
Name VARCHAR(200) /* NOT UNIQUE */
);
If I want the Name(s) that is(are) the most frequent and the corresponding count(s), I can neither do this
SELECT Name, total
FROM table2
WHERE total = (SELECT MAX(total) FROM (SELECT Name, COUNT(*) AS total
FROM MyTable GROUP BY Name) table2);
nor this
SELECT Name, total
FROM (SELECT Name, COUNT(*) AS total FROM MyTable GROUP BY Name) table1
WHERE total = (SELECT MAX(total) FROM table1);
Also, (let’s say the maximum count is 4) in the second proposition, if I replace the third line by
WHERE total = 4;
it works.
Why is that so?
Thanks a lot
2
Answers
You can try the following:
Your queries can’t be executed because "total" is no column in your table. It’s not sufficient to have it within a sub query, you also have to make sure the sub query will be executed, produces the desired result and then you can use this.
You should also consider to use a window function like proposed in Dimi’s answer.
The advantage of such a function is that it can be much easier to read.
But you need to be careful since such functions often differ depending on the DB type.
If you want to go your way with a sub query, you can do something like this:
I created a fiddle example which shows both queries mentioned here will produce the same and correct result:
db<>fiddle