skip to Main Content

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


  1. You can try the following:

    WITH stats as
    (
    SELECT Name
          ,COUNT(id) as count_ids
    FROM MyTable
    GROUP BY Name
    )
    
    SELECT Name
          ,count_ids
    FROM
    (
    SELECT Name
          ,count_ids
          ,RANK() OVER(ORDER BY count_ids DESC) as rank_ -- this ranks all names
    FROM stats
    ) s
    WHERE rank_ = 1 -- the most popular ```
    
    This should work in TSQL.
    
    Login or Signup to reply.
  2. 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:

    SELECT name, COUNT(name) AS total FROM myTable
    GROUP BY name
    HAVING COUNT(name) =
    (SELECT MAX(sub.total) AS highestCount FROM
    (SELECT Name, COUNT(*) AS total
    FROM MyTable GROUP BY Name) sub);
    

    I created a fiddle example which shows both queries mentioned here will produce the same and correct result:
    db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search