表A如下:
列cola colb colc
a 3 1
a 2 2 <--
b 9 3
b 10 4 <--我需要根据Cola分组,然后在同组内按ColC排序,取ColC值最大的那行的ColB值,即
如何通过查询得到这样的结果:
列cola colb colc
a 2 2
b 10 4
列cola colb colc
a 3 1
a 2 2 <--
b 9 3
b 10 4 <--我需要根据Cola分组,然后在同组内按ColC排序,取ColC值最大的那行的ColB值,即
如何通过查询得到这样的结果:
列cola colb colc
a 2 2
b 10 4
FROM tb AS A
WHERE NOT EXISTS(SELECT * FROM tb WHERE COLA=A.COLA AND COLC>A.COLC)
ORDER BY COLA;
go
insert into a
select 'a',3,1
union all
select 'a',2,2
union all
select 'b',9,3
union all
select 'b',10,4
go
select a.cola,a.colb,a.colc
from a
inner join (
select cola,max(colc) as colc
from a
group by cola
) list on list.cola = a.cola and list.colc = a.colc
order by a.cola