Selection of upper values ​​without restrictions

Question

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 level in the country / countries with the most languages ​​that are spoken .

I wrote this request

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 this, obviously, only works if I have one country with the maximum number of languages, what can I do if there is more than one country with the maximum number of languages?

Answer 1

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;

Answer 2

Just add another subquery that returns the maximum number of languages:

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