Partition By in SQL Query

Posted: August 20, 2011 in SQLServer
Tags: ,
Getting department wise top salaried employee records ...
DECLARE @EMPLOYEE TABLE (
EmployeeId INT,
DepartmentId INT,
EmpSal MONEY
)
INSERT INTO @EMPLOYEE (EmployeeId,DepartmentId,EmpSal)
SELECT 100, 1, 25000 UNION ALL
SELECT 101, 1, 35000 UNION ALL
SELECT 102, 1, 20000 UNION ALL
SELECT 103, 2, 27000 UNION ALL
SELECT 104, 2, 37000 UNION ALL
SELECT 105, 2, 26000

SELECT  departmentid, employeeid, empsal FROM
(SELECT departmentid, employeeid, empsal, DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY empsal desc)  Salrank FROM @EMPLOYEE) EmployeeDataWithRank
WHERE salrank = 1;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s