求SQL - Oracle / 开发

列A，列B
a1,1
a1,2
a1,3
a2,1
a2,2
a3,1
a4,2
..
..


取
列A=a1里列B最大的
列A=a2里列B最大的
。。。。

---

select a ,max(b) as maxb from table group by a

---

select a, max(b)
from tb
group by a

---

引用

select a ,max(b) as maxb from table group by a

列很多。
我想写 select * 呢？

---

SQL code:
select a ,max(b) as maxb from table group by a

---

引用

引用 1 楼 fardison 的回复:
select a ,max(b) as maxb from table group by a


列很多。
我想写 select * 呢？

用group by 就不能用*

---

也就是说除了 group by 没有别的办法？

---

楼上正解
SQL code:
with Table_A as (

select 'a1' a ,1 b from dual
union all
select 'a1' a ,2 b from dual
union all
select 'a1' a ,3 b from dual
union all
select 'a2' a ,1 b from dual
union all
select 'a2' a ,2 b from dual
union all
select 'a3' a ,1 b from dual
union all
select 'a4' a ,2 b from dual

)

SELECT *
from Table_A t1
WHERE EXISTS (SELECT t2.a, max(t2.b)
from Table_A t2
WHERE t2.a=t1.a
GROUP BY t2.a)