id aa other 1 10 a 1 20 b 1 30 d 2 100 f 2 200 g 3 50 h 要求得到如下记录(既显示不同id的最大aa值): id aa other 1 30 d 2 200 g 3 50 h
select id,max(aa) from user.table group by id
select id,max(aa),other from user.table group by id,other
kingofworl(良辰美景虚度 说的不对
select id ,aa ,other from ( select id ,aa, other, rank()over(partition by id order by aa desc) rank_aa from test ) where rank_aa = 1;
如果有Other的话就复杂一点 select * from table a, (select id,max(aa) aa from table group by id) b where a.id=b.id and a.aa=b.aa
select id,max(aa) from t group by id
select * from table where aa in(select max(aa) from table group by id);
select id ,aa ,other from ( select id ,aa, other, rank()over(partition by id order by aa desc) rank_aa from test ) where rank_aa = 1;刚才写错了 ,上面这个用分析函数的写法是效率最高的
凑个热闹哈哈! SELECT * FROM (SELECT a.*, row_number()over(partition BY f_NodeID ORDER BY f_ParentID DESC)AS f_Max FROM t_Test a) WHERE f_Max =1
select id ,aa ,other from ( select id ,aa, other, rank()over(partition by id order by aa desc) rank_aa from test ) where rank_aa = 1;
1 10 a
1 20 b
1 30 d
2 100 f
2 200 g
3 50 h
要求得到如下记录(既显示不同id的最大aa值):
id aa other
1 30 d
2 200 g
3 50 h
group by id
group by id,other
select id ,aa, other, rank()over(partition by id order by aa desc) rank_aa
from test )
where rank_aa = 1;
select *
from table a,
(select id,max(aa) aa
from table
group by id) b
where a.id=b.id
and a.aa=b.aa
select id ,aa, other, rank()over(partition by id order by aa desc) rank_aa
from test )
where rank_aa = 1;刚才写错了 ,上面这个用分析函数的写法是效率最高的
SELECT * FROM (SELECT a.*, row_number()over(partition BY f_NodeID ORDER BY f_ParentID DESC)AS f_Max FROM t_Test a) WHERE f_Max =1
select id ,aa, other, rank()over(partition by id order by aa desc) rank_aa
from test )
where rank_aa = 1;