select a.id, a.type, a.ver
from tab a,
(select type, max(ver) ver
from tab
group by type) b
where a.type = b.type
and a.ver = b.ver
from tab a,
(select type, max(ver) ver
from tab
group by type) b
where a.type = b.type
and a.ver = b.ver
id name type1 type2 ver
------------------------------------
1 aaa 1 a 100
2 bbb 1 b 102
3 ccc 1 d 105
4 bbb 1 b 101
5 aaa 1 a 111
6 ddd 1 d 121
7 aaa 2 a 131
8 bbb 2 b 100
9 ccc 2 d 105
10 bbb 2 b 101
11 aaa 2 a 111
12 ddd 2 d 121结果应该是:
tab
id name type1 type2 ver
------------------------------------
2 bbb 1 b 102
3 ccc 1 d 105
5 aaa 1 a 111
6 ddd 1 d 121
7 aaa 2 a 131
9 ccc 2 d 105
10 bbb 2 b 101
12 ddd 2 d 121
请问SQL应该如何写?
from tab a,
(select type1,type2, max(ver) ver
from tab
group by type1,type2) b
where a.type1 = b.type1
and a.type2 = b.type2
and a.ver = b.ver
Select t.id, t.fname, t.type1, t.type2, t.ver
from (select id,
fname,
type1,
type2,
ver,
row_number() Over(Partition by type1, type2 order by ver desc) As Rid
from tab) t
Where t.rid = 1