select id,name1,name2,value from
(select row_number() over(partition by name1||name2 order by value ) rn,
id,name1,name2,value from table1)
where rn = 1;
(select row_number() over(partition by name1||name2 order by value ) rn,
id,name1,name2,value from table1)
where rn = 1;
select * from a where rowid in
(select rowid from
(select Name1,Name2,min(Value),min(rowid) from a group by Name1,Name2)
)
select id,name1,name2,value from
(select rank() over(partition by name1,name2 order by value ) rk,
id,name1,name2,value from table1)
where rk= 1;
from a group by Name1,Name2
select * from table1 where (name1,name2,value) in (select name1,name2,min(value) from table1 group by name1,name2)
where value=(select min(value) from table1
where name1=t.name1 and name2=t.name2 )
------ ------------------------------ ------------------------------ ------
1 aaa bbb 20
2 aaa bbb 10
3 bbb ccc 50
4 bbb ccc 30
5 ccc ddd 60SQL>
SQL> select id,name1,name2,value from test
2 where (name1,name2,value) in
3 ( select name1,name2,min(value)
4 from test
5 group by name1,name2
6 )
7 ; ID NAME1 NAME2 VALUE
------ ------------------------------ ------------------------------ ------
2 aaa bbb 10
4 bbb ccc 30
5 ccc ddd 60SQL>
select * from table1 where (name1,name2,value) in (select name1,name2,min(value) from table1 group by name1,name2)
如果同一组中value相同的有两条,那么最后会把这两条都选出来的
t1.value = (Select min(value) from demo t2 where t2.name1 = t1.name1 and t2.name2 = t1.name2)