CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Eric Casera
Eric Casera

Reputation: 57

Selecting top values without limit

I have the following relational schema:

Country(code: Str, name: Str, capital: Str, area: int)
code (this is the usual country code, e.g. CDN for Canada, F for France, I for Italy)
name (the country name) capital (the capital city, e.g. Rome for Italy)
area (The mass land the country occupies in square Km)

Economy (country: Str, GDP: int, inflation: int, military: int, poverty: int)
country is FK  to the Country table
GDP (gross domestic product)
inflation (annual inflation rate)
military (military spending as percentage of the GDP)
poverty rate (percentage of population below the poverty line)  

Language (country: Str, language: Str, percentage: int)
country is FK  to the Country table
language is a spoken language name
percentage (percentage of population speaking the language)

I need to write a query that finds the poverty rate in the country/countries with the largest number of languages spoken.

I wrote this query

SELECT poverty
FROM(
  SELECT COUNT(language) as langnum, country
  FROM "Language"
  GROUP BY country) AS conto
JOIN "Economy" AS E
ON E.country=conto.country
ORDER BY conto.langnum DESC
LIMIT 1

And it obviously works only if i have one country with the max number of languages, what can i do if there is more than one country with the max number of languages?

Upvotes: 4

Views: 270

Answers (2)

Jan
Jan

Reputation: 13858

Just add another subselect returning the maximum number of languages there are:

SELECT poverty, E.country
FROM(
  SELECT COUNT(language) as langnum, country
  FROM "Language"
  GROUP BY country) AS conto
JOIN "Economy" AS E
ON E.country=conto.country
JOIN (
SELECT COUNT(language) as langnum, country
  FROM "Language"
  GROUP BY country ORDER BY 1 DESC LIMIT 1
) AS maxlang ON maxlang.langnum = conto.langnum
ORDER BY conto.langnum DESC

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269853

Use rank() or dense_rank():

SELECT poverty
FROM (SELECT COUNT(language) as langnum, country,
             RANK() OVER (ORDER BY COUNT(language) DESC) as ranking
      FROM "Language"
      GROUP BY country
     ) conto JOIN "Economy" AS E
     ON E.country=conto.country
WHERE conto.ranking = 1
ORDER BY conto.langnum DESC;

Upvotes: 2

Related Questions