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 m.year, COUNT(*)
FROM movies as m
GROUP BY m.year
ORDER BY m.year DESC) AS topTwo
ORDER BY **topTwo** ASC
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;
LIMITgoes in the subquery.
COUNT(*)is given an alias.
ORDER BYin the subquery is based on the count.
ORDER BYin the outer query is based on the year.