Как найти max от count

Ответ 1

Использование:

  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

Заказ num_movies DESC будет содержать самые высокие значения в верхней части набора результатов. Если многолетний период имеет одинаковый счет, m.yr поместит последний год вверху… до следующего значения num_movies.

Нет, вы не можете группировать агрегированные функции друг над другом в том же предложении SELECT. Внутренний агрегат должен выполняться в подзапросе. IE:

SELECT MAX(y.num)
  FROM (SELECT COUNT(*) AS num
          FROM TABLE x) y

Ответ 2

Просто закажите count(*) desc, и вы получите наивысший результат (если вы объедините его с limit 1)

Ответ 3

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 < 2

Ответ 4

с этого сайта – http://sqlzoo.net/3.htm
2 возможных решения:

с 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)

с 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)

Ответ 5

Использование max с лимитом даст вам только первую строку, но если есть две или более строк с одинаковым количеством максимальных фильмов, вы можете пропустить некоторые данные. Ниже приведен способ сделать это, если у вас есть функция rank().

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

Ответ 6

Следующий код дает вам ответ. Он по существу реализует MAX (COUNT (*)), используя ALL. Преимущество состоит в том, что он использует очень простые команды и операции.

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) >= 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)

Ответ 7

В зависимости от используемой базы данных…

select yr, count(*) num from ...
order by num desc

Большая часть моего опыта в Sybase, который использует какой-то другой синтаксис, чем другие БД. Но в этом случае вы назовете свой столбец count, чтобы вы могли сортировать его по убыванию. Вы можете сделать еще один шаг и ограничить результаты до первых 10 строк (чтобы найти его 10 самых загруженных лет).

Ответ 8

     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

Ответ 9

^{Этот вопрос старый, но был указан в новом вопросе на dba.SE. Я чувствую, что лучшие решения не были предоставлены, но я добавляю еще один.}

Прежде всего, при условии ссылочной целостности (как правило, с ограничениями внешних ключей), вам не нужно вообще присоединяться к таблице movie. Этот мертвый груз в вашем запросе. Все ответы пока не указывают на это.

Можно ли сделать max(count(*)) в SQL?

Чтобы ответить на вопрос в заголовке: Да, в Postgres 8.4 (выпущен 2009-07-01, перед тем, как этот вопрос был задан) или позже, вы можете добиться этого, вложив агрегированную функцию в функция окна:

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;

Рассмотрим последовательность событий в запросе SELECT:

• Лучший способ получить результат до применения LIMIT

(возможный) недостаток: функции окна не группируют строки. Вы получаете все строки, оставшиеся после совокупного шага. Полезно в некоторых запросах, но не идеально для этого.

Чтобы получить одну строку с наивысшим счетчиком, вы можете использовать ORDER BY ct LIMIT 1 как @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;

Использование только базовых функций SQL, доступных в любой половине допустимой РСУБД – реализация LIMIT различается:

• Элементы выбора SQL, где сумма поля меньше N

Или вы можете получить одну строку на группу с наивысшим счетчиком с помощью DISTINCT ON (только Postgres):

• Выберите первую строку в каждой группе GROUP BY?

Ответ

Но вы просили:

… строки, для которых count (*) max.

Возможно, более одного. Наиболее элегантным решением является функция окна rank() в подзапросе. Ryan предоставил запрос, но это может быть проще (подробности в моем ответе выше):

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;

В настоящее время все основные функции поддержки RDBMS поддерживаются. За исключением MySQL и forks (MariaDB, похоже, их реализовала наконец в версии 10.2).

Ответ 10

Благодаря последнему ответу

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) >= 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)

У меня была такая же проблема, мне нужно было знать только те записи, которые их счетчик соответствует максимальному счету. (это может быть одна или несколько записей)

Мне нужно узнать больше о “ALL clause”, и это именно то простое решение, которое я искал.

Ответ 11

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

—– СМОТРЕТЬ СМОТРЕТЬ —–

select yr from sal
where ct =(select max(ct) from sal)

Ю.Р.
2013