CODEWITHSUNDEEP
mysqlsqlgroup-bymax
lcm
lcm

Reputation: 1767

Selecting only the highest value for a specific record with other unique columns?

I have table that contains data similar to the following. The query that I need would result in the top result for Orange and Apple that has the highest cityID value. 

id | fruit  | attr | cityID | personID 
163 Apple   green   3685    235
163 Apple   red     3145    267
163 Apple   yellow  1522    560
164 Orange  big     1344    147
164 Orange  small   833     2673

The results I need would be 

id | fruit  | attr | cityID | personID 
163 Apple   green   3685    235
164 Orange  big     1344    147

I started trying to accomplish this with 

select 
   fruit_id,
   fruit,
   attr, 
   max(cityID),
   personID
from fruits_cities
    group by
     fruit_id,
     fruit,
     attr, 
     max(cityID),
     personID

Upvotes: 0

Views: 30

Answers (2)

James Dullat
James Dullat

Reputation: 67

The shortest code that may helps you.

SELECT fruit_id,fruit,attr,max(cityID),personID FROM `fruits_cities` GROUP BY fruit ORDER BY fruit_id

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269593

This is a challenge. You need to use some sort of comparison. Here is an easy way:

select fc.* 
from fruits_cities fc
where fc.cityId = (select max(CityId)
                   from fruits_cities fc2
                   where fc2.fruit_id = fc.fruit_id
                  );

Upvotes: 2

Related Questions