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

by

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

More Related Contents:

  1. Conditional lead/lag function PostgreSQL?
  2. Sort by column ASC, but NULL values first?
  3. Return row with the max value of one column per group [duplicate]
  4. ORDER BY the IN value list
  5. Get top results for each group (in Oracle)
  6. Fetch the row which has the Max value for a column
  7. Oracle SQL query: Retrieve latest values per group based on time [duplicate]
  8. SQL best practice to deal with default sort order
  9. Calculating Cumulative Sum in PostgreSQL
  10. How to order by with union in SQL?
  11. Optimize query with OFFSET on large table
  12. How to use DISTINCT and ORDER BY in same SELECT statement?
  13. GROUP BY and aggregate sequential numeric values
  14. Fetch the rows which have the Max value for a column for each distinct value of another column
  15. What’s the difference between RANK() and DENSE_RANK() functions in oracle?
  16. PostgreSQL equivalent for TOP n WITH TIES: LIMIT “with ties”?
  17. Get the distinct sum of a joined table column
  18. T-SQL: Selecting Column Based on MAX(Other Column)
  19. How to update and order by using ms sql
  20. Window Functions: last_value(ORDER BY … ASC) same as last_value(ORDER BY … DESC)
  21. SQL query to get most recent row for each instance of a given key
  22. “Order By” using a parameter for the column name
  23. SQL query to select distinct row with minimum value
  24. SQL server select distinct rows using most recent value only
  25. Custom Order in Oracle SQL
  26. Get values from first and last row per group
  27. SQL for ordering by number – 1,2,3,4 etc instead of 1,10,11,12
  28. SQLite Order By Date1530019888000
  29. How to aggregate over rolling time window with groups in Spark
  30. Curious issue with Oracle UNION and ORDER BY

Leave a Comment