ld4795 ld4795 - 1 month ago 11
SQL Question

QL: Find Top 2 and reverse order

I am having the IMDB database; I am looking for the top two years in which most movies were produced, and I have to sort them chronologically after the years and print only the years.

I am trying this to compute the list and sort it 'the other way around' afterwards but I cannot order by anthing in the last 'order by' statement because in the FROM-statement I dont refer to any tables and instead open the next statement. It says "unknown column topTwo" as well so that I cannot order my results accordingly.

What am I doing wrong?

SELECT *
FROM

(SELECT m.year, COUNT(*)
FROM movies as m
GROUP BY m.year
ORDER BY m.year DESC) AS topTwo

ORDER BY **topTwo** ASC
LIMIT 2;

Answer

I think you are looking for this:

SELECT topTwo.year
FROM (SELECT m.year, COUNT(*) as cnt
      FROM movies m    
      GROUP BY m.year    
      ORDER BY COUNT(*) DESC
      LIMIT 2
     ) topTwo    
ORDER BY year ASC; 

Notes:

  • The LIMIT goes in the subquery.
  • The COUNT(*) is given an alias.
  • The ORDER BY in the subquery is based on the count.
  • The ORDER BY in the outer query is based on the year.
  • You only seem to want the year, so the outer query only select that column.
Comments