Get top 2 rows per group by

Given a Commissions table with a Commission (decimal) and an employeeID primary key columns,  list the top 2 commissions of each employee:

Best is to use a CTE with either the ROW_NUMBER() or RANK() partitions:

 ;With CTE_MyRank as (
SELECT [Commission]
      ,[EmployeeId]
      ,[CommissionID]
 , ROW_NUMBER() over (partition by employeeID order by commission desc) as Rankx
  FROM [Training].[dbo].[Commissions] 
  )

  select  EmployeeID, commission, Rankx from CTE_MyRank where rankx < 3 order  by [EmployeeId] desc, commission desc



OR the less favorite way is to use MAX , Group By and a UNION:

  Select  Max([Commission]) xMax, [EmployeeId] from [Training].[dbo].[Commissions]
   Group by [EmployeeId] 
  UNION
 Select Max(Commission) xMax, [EmployeeId] from [Training].[dbo].[Commissions] 
 where (Commission) not in (select Max([Commission]) from [Training].[dbo].[Commissions] 
 Group by [EmployeeId] )

Group by [EmployeeId] 
   order by [EmployeeId] desc, xMAx desc