Oracle 怎么求最大值的行

[yourlass]

Oracle 中有如下行

ID value
1 1
1 2
2 1
2 2
2 3

用什么样的 SQL 能得到
ID value
1 2
2 3

[CT_LXL]

with t as
 (select 1 id, 1 value
    from dual
  union all
  select 1 id, 2 value
    from dual
  union all
  select 2 id, 1 value
    from dual
  union all
  select 2 id, 3 value
    from dual
  union all
  select 2 id, 2 value from dual)
select t1.id, t1.value
  from (select t.*,
               row_number() over(partition by id order by value desc) rn
          from t) t1
 where t1.rn = 1;

[佛山大虾]

SELECT id,max(VALUE) FROM 表名 GROUP BY id;

[gangma2]

和我前两天解决的问题差不多，http://bbs.csdn.net/topics/390718983。比那个简单了点，

select id,max(value) from bb group by id;

[空心兜兜]

over partition by 这个很经典

[wangpengpengwang]

分析函数啊嘎嘎嘎

[yiyongle]

你这是删除重复的数据只保留value最大的一条，可以用SELECT id,max(VALUE)FROM 表名 GROUP BY id;

[yiyongle]

也可以用这条语句 delete from 表名 where ID in (select ID from 表名 group by ID having count(ID) > 1) and value not in (select min(value) from 表名 group by ID having count(ID)>1)

[nathanjiang]

over parition by 很给力，谢谢各位！