1

I'm trying to find which country has highest life expectancy in my database. This is what I have so far:

SELECT Name, MAX(LifeExpectancy)
FROM country

However, this outputs the Max life expectancy, and an irrelevant Country name (I think it's showing the first one alphabetically) rather than the Country that correlates with the max life expectancy.

How do you select the country name with the highest max life expectancy?

Dima
  • 27
  • 1
  • 8
  • If it's a tie, two different countries have the same highest life expectancy, what's the expected result? – jarlh Apr 18 '17 at 06:47

2 Answers2

1

One option would be to select all records from country, order the result set descending by life expectancy, and retain only the first record.

SELECT Name, LifeExpectancy
FROM country
ORDER BY LifeExpectancy DESC
LIMIT 1

If there could be a tie between two or more countries for the maximum life expectancy, then you could also try the following:

SELECT Name, LifeExpectancy
FROM country
WHERE LifeExpectancy = (SELECT MAX(LifeExpectancy) FROM country)
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

You can find it using subquery.

SELECT Name, LifeExpectancy
FROM country WHERE LifeExpectancy = (SELECT MAX(LifeExpectancy) FROM country)
ProgrammerBoy
  • 876
  • 6
  • 19
  • This is a late/duplicate answer (and has virtually no explanation) please practice good SO citizenship and delete this answer. – mickmackusa Apr 18 '17 at 07:23