我有一个id,年和数的表.
我想得到每个id的MAX(count),并保持发生的一年,所以我做这个查询：
SELECT id,year,MAX(count) FROM table GROUP BY id;
不幸的是,它给我一个错误：
ERROR: column “table.year” must appear in the GROUP BY clause or be
used in an aggregate function
所以我试试：
SELECT id,MAX(count) FROM table GROUP BY id,year;
但是,它不做MAX(计数),它只是显示表.我想,因为当按年份和身份分组时,它会获得该特定年份的最大值.
那么,怎么写这个查询呢？我想得到ID的MAX(计数)和发生的那一年.
select * from ( select id,thing,max(thing) over (partition by id) as max_thing from the_table ) t where thing = max_thing
要么：
select t1.id,t1.year,t1.thing
from the_table t1
where t1.thing = (select max(t2.thing)
from the_table t2
where t2.id = t1.id);
要么
select t1.id,t1.thing
from the_table t1
join (
select id,max(t2.thing) as max_thing
from the_table t2
group by id
) t on t.id = t1.id and t.max_thing = t1.thing
或(与以前的不同符号相同)
with max_stuff as (
select id,max(t2.thing) as max_thing
from the_table t2
group by id
)
select t1.id,t1.thing
from the_table t1
join max_stuff t2
on t1.id = t2.id
and t1.thing = t2.max_thing
