CODEWITHSUNDEEP
mysqlsql
Don P
Don P

Reputation: 63567

Select a value where another column has the highest value

I have a table with people, their age, and their awesomeness at each age.

What is the simplest query to get John's 'awesomeness' at their maximum age?

People

Name    Age    Awesomeness
Don    1      12
Don    2      23
Don    3      43
Don    4      30
Sam     1      9
Sam     2      18
Sam     3      59
Sam     4      99

The best query I have:

SELECT awesomeness
FROM people
JOIN (
  SELECT MAX(age)
  FROM people
  WHERE name = 'Don'
) a 
ON people.age = a.age
WHERE people.name = 'Don'

Upvotes: 1

Views: 38

Answers (2)

O. Jones
O. Jones

Reputation: 108641

You may be wishing to show everyone's score at their maximum age.

You can do that with this query: http://sqlfiddle.com/#!2/b0ff4/1/0

SELECT a.name, a.awesomeness
  FROM people a
  JOIN (
         SELECT name, MAX(age) age
           FROM people
          GROUP by name
       ) b ON a.name = b.name AND a.age=b.age

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269593

Just use order by and limit:

SELECT awesomeness
FROM people
WHERE people.name = 'Don' 
ORDER BY age desc
LIMIT 1

Upvotes: 1

Related Questions