group by 用法
表My_allScore字段OrderId,AttemptId , RegID , EmpId ,StartDt ,EndDt ,Success ,Score ,EmployeeNumber ,EmpFName ,EmpLName ,PrimaryDomain
--1.求每个员工的最高成绩和AttemptId  
不查AttemptId结果是对的，但是最高成绩有重复，所以要查AttemptId，但是结果又不对了。
SELECT max(Score),MS.AttemptId,EmpId FROM #My_allScore group by MS.EmpId ,MS.AttemptId order by EmpId
--2.求每个员工的最近一次和AttemptId

------解决方案--------------------

SQL code

1 select A.empid,A.score,A.attemptid
  from My_allScore A
  where not exists (select 1 from My_allScore B where A.EmpId =B.EmpId and A.Score<B.Score )

2 select A.empid,A.attemptid
  from My_allScore A
  where not exists (select 1 from My_allScore B where A.EmpId =B.EmpId and A.OrderId<B.OrderId)