MySQL query with COUNT(*) and MAX() returning multiple columns-CodePudding

I am creating a small database to track appearances in Star Wars films. I am trying to write a query to return the actor with the most appearances. My subquery is working correctly.

This gives query gives back the actor first and last name as well as the total number of appearances.

Currently I am trying to use MAX() on the Total. This works correctly and returns the largest from the Total, but I am unsure how to attach the First and Last name fields along with this.

This returns a single column of the name Total Appearances with a value of 9.

CodePudding user response：

You can use RANK() window function:

SELECT
    ACT_FNAME AS 'First Name',
    ACT_LNAME AS 'Last Name',
    Total
FROM (
  SELECT 
      ac.ACT_FNAME,
      ac.ACT_LNAME,
      COUNT(*) AS 'Total',
      RANK() OVER ( ORDER BY COUNT(*) DESC) as 'rank'
  FROM appearance ap
      JOIN actor ac ON ap.ACT_ID = ac.ACT_ID
  GROUP BY ap.ACT_ID
) as q
WHERE rank=1

In older MySQL you could do:

SELECT 
  totals.ACT_FNAME  AS 'First Name',
  totals.ACT_LNAME AS 'Last Name', 
  totals.Total
FROM (
  SELECT 
      ac.ACT_FNAME,
      ac.ACT_LNAME,
      COUNT(*) AS 'Total'
  FROM appearance ap
    JOIN actor ac ON ap.ACT_ID = ac.ACT_ID
  GROUP BY ap.ACT_ID
) as totals
  JOIN (
    SELECT MAX(cnt) as MAX_CNT
    FROM (
      SELECT ACT_ID, COUNT(*) as 'cnt'
      FROM appearance 
      GROUP BY ACT_ID
    ) as q
  ) ap_max 
WHERE totals.Total=ap_max.MAX_CNT