使用GROUP BY选择多个(非聚合函数)列 [英] Select multiple (non-aggregate function) columns with GROUP BY
问题描述
我试图从一列中选择最大值,同时按具有多个重复值的另一个非唯一id列进行分组.原始数据库如下所示:
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;
哪个返回如下表:
mukey | ComponentPercent
65789 | 20
65790 | 26
65791 | 50
65792 | 90
我希望能够在不影响GROUP BY函数的情况下添加其他列,以将诸如name和type之类的列包括在输出表中,例如:
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
但是它总是输出一个错误,说我需要在select语句中使用聚合函数.我应该怎么做呢?
but it always outputs an error saying I need to use an aggregate function with select statement. How should I go about doing this?
推荐答案
您拥有一个 greatest-n-per-group 问题.这是可能的解决方案之一:
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
这篇关于使用GROUP BY选择多个(非聚合函数)列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!