SQL query and subqueries

Question

I have a question for all the SQL experts.

There is table looking like:

 clanname  country     points
 name1     country1    100
 name2     country1    90
 name3     country1    10
 name4     country2    100
 name5     country2    80
 name6     country2    70

I would like to make a country ranking involving only the top2 results of each country. so, in this example, the ranking should be:

 country    average-points
 country2   95
 country1   90

If there a way to get this result with only one SQL query using subqueries?

In reality, I have more than 200 countries.. and thousands of results for each country. But I'd like to filter only the top 30 results of each country.

Right now I managed to get the average of one country using this query:

  SELECT 
      location, AVG(warswon)
  FROM
      (SELECT 
           `name`, `location`, `warswon` 
       FROM 
           `clans`
       WHERE 
           location = 'China'
       ORDER BY 
           `clans`.`warswon` DESC 
       LIMIT 30) AS top30ofcountry

but how do I get the average results of each country in one query?

Is this possible?

Answer 1

Thanks for the help Conrad. I managed to get the reqested result by only changing slightly the code proposed by you. Here is the final working query for those who have similar questions:

SELECT 
location,
avg(warswon)
FROM
(
SELECT 
@num := if(@group = `location`, @num + 1, 1) as row_number,
@group := `location` as dummy,
name,
location,
warswon
FROM
clans
JOIN (SELECT @group := NULL, @num := 0) as z
order by
location, warswon desc
) as x 
WHERE x.row_number <= 30
GROUP BY `location`
ORDER BY avg(warswon) DESC

Answer 2

This is n per group problem. This is trivial in many DB's using row_number. In MySQL you can do it with user variables

SELECT 
  country ,
  avg(points)
FROM
(
  SELECT 
        @num := if(@group = `country`, @num + 1, 1) as row_number,
        @group := `country` as dummy,
        clanName,
        country,
        points
    FROM
      clans
      JOIN (SELECT @group := NULL, @num := 0) as z
     order by
        country, points desc) as x 
WHERE x.row_number <=2 
GROUP BY 
    country