我的桌子：
ID NUM VAL 1 1 Hello 1 2 Goodbye 2 2 Hey 2 4 What's up? 3 5 See you
如果我想要返回每个ID的最大数量,这真的很好,干净：
SELECT MAX(NUM) FROM table GROUP BY (ID)
但是,如果我想抓住与每个ID的每个号码的最大值相关联的值呢？
为什么我不能做：
SELECT MAX(NUM) OVER (ORDER BY NUM) FROM table GROUP BY (ID)
为什么是错误？我想要这个选择按ID分组,而不是分别为每个窗口划分…
编辑：错误是“不是GROUP BY表达式”.
你可以使用MAX()KEEP(DENSE_RANK LAST …)函数：
with sample_data as ( select 1 id,1 num,'Hello' val from dual union all select 1 id,2 num,'Goodbye' val from dual union all select 2 id,'Hey' val from dual union all select 2 id,4 num,'What''s up?' val from dual union all select 3 id,5 num,'See you' val from dual) select id,max(num),max(val) keep (dense_rank last order by num) from sample_data group by id;
