Hopefully this is the correct place to ask this question. In this SQL cross-join exercise from codeacademy with the following code:

SELECT month, COUNT(*) FROM newspaper
CROSS JOIN months
  WHERE (start_month<=month) & (end_month>=month)
GROUP BY month;

Returns a table

How can I then retrieve the row with the max COUNT(*) from this table? {month:6, COUNT( * ):30}?

I tried the following which doesn't work (returns blank on the website):

SELECT * FROM 
  (SELECT month, COUNT(*) FROM newspaper
    CROSS JOIN months
      WHERE (start_month<=month) & (end_month>=month)
    GROUP BY month)
WHERE COUNT(*)=(
  SELECT MAX(COUNT(*)) FROM
   (SELECT month, COUNT(*) FROM newspaper
    CROSS JOIN months
      WHERE (start_month<=month) & (end_month>=month)
    GROUP BY month)
    );

Preferably, I would to this to work without renaming COUNT(*).

P.S: No idea which SQL dialect codeacademy uses.

CodePudding user response：

I realized we don't need a CTE to do this, you can simply do:

SELECT TOP(1) month, COUNT(*) FROM newspaper
CROSS JOIN months
  WHERE (start_month<=month) & (end_month>=month)
GROUP BY month
ORDER BY 2 DESC
;

This will grab the top row, and it will be ordered by the highest count. I am unsure of language used by CodeAcademy, but every language I know of can grab the top row in some fashion.