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
Tuesday, October 27, 2015
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment