CODEWITHSUNDEEP
mysqlsqlgroup-bygrouping
xiaolong
xiaolong

Reputation: 3647

SQL Grouping: conditional select in each group

For example, I got a table:

Name  , Area,   Age
cat   ,   1 ,    10
dog   ,   2 ,     7
cat   ,   3 ,    11
horse ,   4 ,     4
cat   ,   5 ,    10
dog   ,   6 ,     9

When I group by 'Name', for each group I want to retain the 'Area' of the MAX 'Age' in that group. In this example, I wanna get:

Name , Area
cat  ,  3
dog  ,  6
horse,  4

How should I do this in one query? Thanks guys!

Upvotes: 1

Views: 1039

Answers (3)

PinnyM
PinnyM

Reputation: 35533

This would be more slightly more efficient than the subquery approach mentioned by @deroby and @DamienBlack:

SELECT t1.name, t1.area
FROM myTable t1
LEFT JOIN myTable t2 
  ON t1.name = t2.name AND t2.Age > t1.Age
WHERE t2.some_primary_key IS NULL
ORDER BY t1.name

Note that this requires some column known to contain a value that is not NULL (such as a primary key). You can substitute, t2.some_primary_key with any other non-null, indexed column as needed.

SQLFiddle based off @deroby's here.

Upvotes: 2

Damien Black
Damien Black

Reputation: 5647

Try something like this:

SELECT name, area FROM mytable
JOIN (
    SELECT name, MAX(age) as maxage FROM mytable
    GROUP BY name
) AS `max`
ON  mytable.name = max.name AND mytable.age = max.maxage

This first selects the name and MAX(age) in a subquery, and then joins them to the original table so that you can get the area associated with the MAX(age). By using join ie inner join, we insure that any results in the original table that had nothing to match do not show.

Notice that you can't do something like:

SELECT name, MAX(age), area FROM mytable

Because area would be randomly selected from all the area values in the group. It wouldn't know which area you want. You might think that it would get you the area in the same row as the MAX(age), but it will not. It doesn't actually know that is what you want.

Upvotes: 2

deroby
deroby

Reputation: 6002

Seems I need to learn to type faster =)

Anyway, I came up with this; you can test it in this sqlFiddle

 SELECT t.Name, t.Area
  FROM (SELECT Name, Max(Age) as Max_Age
          FROM test
         GROUP BY Name) mx
  JOIN test t
    ON t.Name = mx.Name
   AND t.Age  = mx.Max_Age
 ORDER BY t.Name

Upvotes: 0

Related Questions