Can I do a max count in SQL

Question

Here s my code       select yr count     from movie join casting on casting movieid movie id join actor on casting actorid   actor id where actor name    John Travolta  group by yr   Here s the question     Which were the busiest years for  John Travolta   Show the number of movies he made for each year    Here s the table structure   movie id  title  yr  score  votes  director  actor id  name  casting movieid  actorid  ord    This is the output I am getting   yr  count    1976    1 1977    1 1978    1 1981    1 1994    1 etcetc   I need to get the rows for which count    is max   How do I do this

User · Answer

This question is old  but was referenced in a new question on dba SE  I feel the best solutions haven t been provided  yet  so I am adding another one  First off  assuming referential integrity  typically enforced with foreign key constraints  you do not need to join to the table movie at all  That s dead freight in your query  All answers so far fail to point that out    Can I do a max count     in SQL   To answer the question in the title  Yes  in Postgres 8 4  released 2009-07-01  before this question was asked  or later you can achieve that by nesting an aggregate function in a window function  SELECT c yr  count    AS ct  max count     OVER    AS max ct FROM   actor   a JOIN   casting c ON c actorid   a id WHERE  a name    John Travolta  GROUP  BY c yr   Consider the sequence of events in a SELECT query   Best way to get result count before LIMIT was applied  The  possible  downside  window functions do not aggregate rows  You get all rows left after the aggregate step  Useful in some queries  but not ideal for this one   To get one row with the highest count  you can use ORDER BY ct LIMIT 1 like  wolph hinted  SELECT c yr  count    AS ct FROM   actor   a JOIN   casting c ON c actorid   a id WHERE  a name    John Travolta  GROUP  BY c yr ORDER  BY ct DESC LIMIT  1   Using only basic SQL features available in any halfway decent RDBMS - the LIMIT implementation varies   SQL select elements where sum of field is less than N  Or you can get one row per group with the highest count with DISTINCT ON  only Postgres    Select first row in each GROUP BY group    Answer But you asked for       rows for which count    is max   Possibly more than one  The most elegant solution is with the window function rank   in a subquery  Ryan provided a query but it can be simpler  details in my answer above   SELECT yr  ct FROM       SELECT c yr  count    AS ct  rank   OVER  ORDER BY count    DESC  AS rnk    FROM   actor   a    JOIN   casting c ON c actorid   a id    WHERE  a name    John Travolta     GROUP  BY c yr      sub WHERE  rnk   1   All major RDBMS support window functions nowadays  Except MySQL and forks  MariaDB seems to have implemented them at last in version 10 2

User · Answer

Thanks to the last answer  SELECT yr  COUNT title  FROM actor JOIN casting ON actor id   casting actorid JOIN movie ON casting movieid   movie id WHERE name    John Travolta  GROUP BY yr HAVING COUNT title   gt   ALL    SELECT COUNT title     FROM actor    JOIN casting ON actor id   casting actorid    JOIN movie ON casting movieid   movie id    WHERE name    John Travolta     GROUP BY yr    I had the same problem  I needed to know just the records which their count match the maximus count  it could be one or several records    I have to learn more about  ALL clause   and this is exactly the kind of simple solution that I was looking for

User · Answer

Just order by count    desc and you ll get the highest  if you combine it with limit 1

User · Answer

select top 1 yr count     from movie join casting on casting movieid movie id join actor on casting actorid   actor id where actor name    John Travolta  group by yr order by 2 desc

User · Answer

Use     SELECT m yr            COUNT    AS num movies     FROM MOVIE m     JOIN CASTING c ON c movieid   m id     JOIN ACTOR a ON a id   c actorid                 AND a name    John Travolta  GROUP BY m yr ORDER BY num movies DESC  m yr DESC   Ordering by num movies DESC will put the highest values at the top of the resultset   If numerous years have the same count  the m yr will place the most recent year at the top    until the next num movies value changes   Can I use a MAX COUNT          No  you can not layer aggregate functions on top of one another in the same SELECT clause   The inner aggregate would have to be performed in a subquery   IE   SELECT MAX y num    FROM  SELECT COUNT    AS num           FROM TABLE x  y

User · Answer

Depending on which database you re using     select yr  count    num from     order by num desc   Most of my experience is in Sybase  which uses some different syntax than other DBs   But in this case  you re naming your count column  so you can sort it  descending order   You can go a step further  and restrict your results to the first 10 rows  to find his 10 busiest years

User · Answer

SELECT   from    SELECT yr as YEAR  COUNT title  as TCOUNT FROM actor JOIN casting ON actor id   casting actorid JOIN movie ON casting movieid   movie id WHERE name    John Travolta  GROUP BY yr order by TCOUNT desc   res where rownum  lt  2

User · Answer

The following code gives you the answer  It essentially implements MAX COUNT     by using ALL  It has the advantage that it uses very basic commands and operations   SELECT yr  COUNT title  FROM actor JOIN casting ON actor id   casting actorid JOIN movie ON casting movieid   movie id WHERE name    John Travolta  GROUP BY yr HAVING COUNT title   gt   ALL    SELECT COUNT title     FROM actor    JOIN casting ON actor id   casting actorid    JOIN movie ON casting movieid   movie id    WHERE name    John Travolta     GROUP BY yr

User · Answer

create view sal as select yr count    as ct from  select title yr from movie m  actor a  casting c where a name  JOHN  and a id c actorid and c movieid m id group by yr   -----VIEW CREATED-----  select yr from sal where ct   select max ct  from sal    YR 2013

User · Answer

it s from this site - http   sqlzoo net 3 htm 2 possible solutions   with TOP 1 a ORDER BY     DESC   SELECT yr  COUNT title   FROM actor  JOIN casting ON actor id actorid JOIN movie ON movie id movieid WHERE name    John Travolta  GROUP BY yr HAVING count title   SELECT TOP 1 COUNT title   FROM casting  JOIN movie ON movieid movie id  JOIN actor ON actor id actorid WHERE name  John Travolta  GROUP BY yr ORDER BY count title  desc    with MAX   SELECT yr  COUNT title   FROM actor   JOIN casting ON actor id actorid     JOIN movie ON movie id movieid WHERE name    John Travolta  GROUP BY yr HAVING      count title            SELECT MAX A CNT               FROM  SELECT COUNT title  AS CNT FROM actor                  JOIN casting ON actor id actorid                 JOIN movie ON movie id movieid                     WHERE name    John Travolta                      GROUP BY  yr   AS A

User · Answer

Using max with a limit will only give you the first row  but if there are two or more rows with the same number of maximum movies  then you are going to miss some data   Below is a way to do it if you have the rank   function available   SELECT     total final yr      total final num movies     FROM       SELECT          total yr           total num movies           RANK   OVER  ORDER BY num movies desc  rnk         FROM                  SELECT                        m yr                         COUNT    AS num movies                FROM MOVIE m                JOIN CASTING c ON c movieid   m id                JOIN ACTOR a ON a id   c actorid                WHERE a name    John Travolta                 GROUP BY m yr                AS total       AS total final     WHERE rnk   1

[sql] Can I do a max(count(*)) in SQL?

Answer

Examples related to sql