Partition By in SQL Query

Posted: August 20, 2011 in SQLServer
Tags: ,
Getting department wise top salaried employee records ...
EmployeeId INT,
DepartmentId INT,
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;

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s