1

I have this table:

first_name last_name age country
John Doe 31 USA
Robert Luna 22 USA
David Robinson 22 UK
John Reinhardt 25 UK
Betty Doe 28 UAE

How can I get only the names of the oldest per country?

When I do this query

SELECT first_name,last_name, MAX(age) 
FROM Customers
GROUP BY country

I get this result:

first_name last_name MAX(age)
Betty Doe 31
John Reinhardt 22
John Doe 31

But I want to get only first name and last name without the aggregate function.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Innuendo
  • 33
  • 4
  • 1
    I'd expect an error. Which dbms are you using? – jarlh Jan 20 '23 at 17:30
  • You also need to _specify_ the expected result. A [mcve]. – jarlh Jan 20 '23 at 17:30
  • I expect: Betty Doe ; John Reinhard; John Doe; – Innuendo Jan 20 '23 at 17:44
  • how do you group by country if you're selecting first_name, last_name? which DBMS are you using? – Kurt Jan 20 '23 at 20:35
  • When asking SQL questions, always tag the request with the DBMS you are using. The query you are showing is invalid according to the SQL standard. You say you are getting a result, though. So what is your DBMS that allows such query? I don't know any DBMS that doesn't through an error and even reports non-maximums with `MAX`. – Thorsten Kettner Jan 20 '23 at 21:53
  • As to the task itself: What result are you expecting when two or more persons share the same maximum age in a country? – Thorsten Kettner Jan 20 '23 at 21:59

2 Answers2

2

If window functions are an option, you can use ROW_NUMBER for this task.

WITH cte AS (
    SELECT *, 
           ROW_NUMBER() OVER(PARTITION BY country ORDER BY age DESC) AS rn 
    FROM tab
)
SELECT first_name, last_name, age, country
FROM cte
WHERE rn = 1

Check the demo here.

lemon
  • 14,875
  • 6
  • 18
  • 38
0

It sounds like you want to get the oldest age per country first,

SELECT Country, MAX(age) AS MAX_AGE_IN_COUNTRY
FROM Customers
GROUP BY Country

With that, you want to match that back to the original table (aka a join) to see which names they match up to.

So, something like this perhaps:

SELECT Customers.*
FROM Customers
INNER JOIN 
(
  SELECT Country, MAX(age) AS MAX_AGE_IN_COUNTRY
  FROM Customers
  GROUP BY Country
) AS max_per_country_query
ON Customers.Country = max_per_country_query.Country
AND Customers.Age = max_per_country_query.MAX_AGE_IN_COUNTRY

If your database supports it, I prefer using the CTE style of handling these subqueries because it's easier to read and debug.

WITH cte_max_per_country AS (
  SELECT Country, MAX(age) AS MAX_AGE_IN_COUNTRY
  FROM Customers
  GROUP BY Country
)
SELECT Customers.*
FROM Customers C
INNER JOIN cte_max_per_country
ON C.Country = cte_max_per_country.Country
AND C.Age = cte_max_per_country.MAX_AGE_IN_COUNTRY
Josh
  • 1,493
  • 1
  • 13
  • 24