Select multiple (non-aggregate function) columns with GROUP BY

Question

I am trying to select the max value from one column, while grouping by another non-unique id column which has multiple duplicate values. The original database looks something like:

mukey    | comppct_r | name | type
65789    | 20        | a    | 7n
65789    | 15        | b    | 8m
65789    | 1         | c    | 1o
65790    | 10        | a    | 7n
65790    | 26        | b    | 8m
65790    | 5         | c    | 1o
...

This works just fine using:

SELECT c.mukey, Max(c.comppct_r) AS ComponentPercent
FROM c
GROUP BY c.mukey;

Which returns a table like:

mukey    | ComponentPercent
65789    | 20
65790    | 26
65791    | 50
65792    | 90

I want to be able to add other columns in without affecting the GROUP BY function, to include columns like name and type into the output table like:

mukey    | comppct_r | name | type
65789    | 20        | a    | 7n
65790    | 26        | b    | 8m
65791    | 50        | c    | 7n
65792    | 90        | d    | 7n

but it always outputs an error saying I need to use an aggregate function with select statement. How should I go about doing this?

Answer 1

You have yourself a greatest-n-per-group problem. This is one of the possible solutions:

select c.mukey, c.comppct_r, c.name, c.type
from c yt
inner join(
    select c.mukey, max(c.comppct_r) comppct_r
    from c
    group by c.mukey
) ss on c.mukey = ss.mukey and c.comppct_r= ss.comppct_r

Another possible approach, same output:

select c1.*
from c c1
left outer join c c2
on (c1.mukey = c2.mukey and c1.comppct_r < c2.comppct_r)
where c2.mukey is null;

There's a comprehensive and explanatory answer on the topic here: SQL Select only rows with Max Value on a Column