sql order by – Can I do a max(count(*)) in SQL?

sql order by – Can I do a max(count(*)) in SQL?

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

Just order by count(*) desc and youll get the highest (if you combine it with limit 1)

sql order by – Can I do a max(count(*)) in SQL?

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

Leave a Reply

Your email address will not be published.