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?

This used to be tagged for Sql Server... does that still apply? Sql Server (and most any other database) makes this easy, because of support for Windowing functions. This will be MUCH harder in MySql than other databases, because it is now the only major database without support for Windowing functions, even though they've been part of the standard for more than 10 years. — Joel Coehoorn
– Joel Coehoorn, Commented Nov 5, 2015 at 17:06
I'm a fan of the ROW_NUMBER function in SQL Server to get the top n per category type of filters. You could add ROW_NUMBER() OVER (PARTITION BY country ORDER BY points DESC) AS RN in a sub query or CTE. Then, group by the country WHERE RN <= your cutoff. — Martin Soles
– Martin Soles, Commented Nov 5, 2015 at 17:06

Conrad Frix · Accepted Answer · 2015-11-05 21:03:41Z

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

edited Nov 5, 2015 at 21:03

answered Nov 5, 2015 at 17:16

Conrad Frix

52.8k13 gold badges100 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Richie Rich Over a year ago

ok conrad.. i have the feeling this is close to the query i need. i use mysql and all the GROUP BY proposals from former post do not help.

Richie Rich · Accepted Answer · 2015-11-06 11:47:38Z

0

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

answered Nov 6, 2015 at 11:47

Richie Rich

292 bronze badges

Collectives™ on Stack Overflow

SQL query and subqueries

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related