How to get values of one column without the aggregate column?-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

Try this:

SELECT first_name, last_name
FROM Customers
WHERE age IN (
               SELECT max(age) 
                 FROM Customers
                GROUP BY country
             );

demo here