How to use results of one sql query within a sub-query

Question

Hey Guys

I have to answer the following question "For each year in the database, list the year and the total number of movies that were released in that year, showing these totals in decreasing order. That is, the year(s) with the largest number of movies appear first. If some years have the same number of movies, show these in increasing order of year." currently i am using the code bellow to get the movies to group but am unable to get them to order.

Select YearReleased, count(*)
from Movies
group by YearReleased

I wish to use a to order this i am trying to make a sub query that uses the results of the first query, along the lines of;

(select * from results order by count(*))

But have been unsuccessful, so how do i achieve this or is there a better way of getting the results to order.

Thanks in advance.

Answer 1

select * from ( Select YearReleased, count(*) as NumReleased from Movies group by YearReleased ) order by NumReleased

Answer 2

select * from (select YearReleased, count(*) counter
from Movies
group by YearReleased 
) a order by counter

May need a syntax change depending on your sql flavour.

Answer 3

"Unsuccessful" isn't very useful, as opposed to actual error text -- and you aren't telling us which vendor's database you're running against, so we can't test. That said, the following should work:

select
  YearReleased,
  count(*) as movie_count
  from movies
  group by YearReleased
  order by movie_count desc, YearReleased;

No subqueries needed!

Validated against SQLite 3.5.9; if running against something less standards-compliant (which SQLite is, except in very explicitly documented ways), your mileage may vary.

Answer 4

in first query: select yearReleased, count(*) as 'count1' in second: order by count1