group by分组问题

[frankl123]

with cte as 
(
	select 1 id,1 personid,2 EducationOrder
	union all
	select 2 id,1 personid,1 EducationOrder
	union all
	select 3 id,2 personid,1 EducationOrder
	union all
	select 4 id,3 personid,1 EducationOrder
)
select * from cte

/*
id personid EducationOrder
----------- ----------- --------------
1 1 2
2 1 1
3 2 1
4 3 1
*/
按照personid分组 取EducationOrder大的行的id值
我想要的结果是
/*
id personid EducationOrder
----------- ----------- --------------
1 1 2
3 2 1
4 3 1
*/

[还在加载中灬]

select * from cte T1
WHERE NOT EXISTS
	(SELECT 1 FROM cte T2 WHERE T1.personid=T2.personid AND T2.EducationOrder>T1.EducationOrder)