CODEWITHSUNDEEP
mysqlsql
user1882491
user1882491

Reputation: 111

Get MAX value based on specific field

Ok, simple question: I have a table

create table people (
  id int auto_increment not null primary key,
  name varchar(32),
  city varchar(32),
  age int
);

with the query

SELECT city, age
FROM people
WHERE age=(SELECT max(age) from people);

I can get the output of the city and age of the oldest person. 

City        Age
Denver      95

How do I get the max age per city, i.e.

City        Age
Atlanta     90
Cincinnati  87
Denver      95

Upvotes: 0

Views: 46

Answers (2)

Rahul
Rahul

Reputation: 77856

Your query can be simplified like

SELECT city, age
FROM people
order by age desc
limit 1

Upvotes: 0

keelerm
keelerm

Reputation: 2943

SELECT city, MAX(age)
FROM people
GROUP BY city

This is a use of MySQL aggregate functions. By providing an additional column and specifying it in the GROUP BY, you are telling MySQL to only apply the aggregation function (in our case MAX) within those matching records.

You can read more about aggregates here.

Upvotes: 2

Related Questions