SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Getting top ranked employee detail for each department

By Sheraz Mirza,

Description:

OVER clause is one of the powerful commands as other analytical functions, it allows you to get aggregate information without using a GROUP BY. In other words, you can retrieve detail rows, and get aggregate data alongside it. Last week, we got a requirement to generate a list of top ranked employee from each department.

Just for the user’s understanding we can create a scenario for the problem.

Each department have different designations, like Director, Manager, Clerk, Supervisor, etc. and the ranking  from top to low is

1)    Director

2)    Manager

3)    Supervisor

4)    Clerk

This listing is company's defult ranking for entire organization, in each department, so if one department doesn't have Director or Manager then Supervisor will be Top Ranked.

Let’s create a Table, insert some dummy records for the Demo.

CREATE TABLE [dbo].[dept_desig]
    (

      [emp_id] [INT] IDENTITY(1, 1)

                     NOT NULL ,

      [dept_name] [VARCHAR](100) NULL ,

      [emp_name] [VARCHAR](50) NULL ,

      [desig] [VARCHAR](100) NULL

    )

ON  [PRIMARY]

GO

Inserting Dummy Records:

INSERT INTO Baseline_DB_Aug_2016.DBO.dept_desig  VALUES ('IT','JAFFERY','DIRECTOR')
INSERT INTO Baseline_DB_Aug_2016.DBO.dept_desig  VALUES ('DEVELOPMENT','CORBIT','PROGRAMMER')
INSERT INTO Baseline_DB_Aug_2016.DBO.dept_desig  VALUES ('DEVELOPMENT','CHANDRA','DBA')
INSERT INTO Baseline_DB_Aug_2016.DBO.dept_desig  VALUES ('IT','KEVIN','MANAGER')
INSERT INTO Baseline_DB_Aug_2016.DBO.dept_desig  VALUES ('IT','ROBERT','SUPERVISOR')
INSERT INTO Baseline_DB_Aug_2016.DBO.dept_desig  VALUES ('QA','NOMAN','ANALYST')
INSERT INTO Baseline_DB_Aug_2016.DBO.dept_desig  VALUES ('ADMIN','CORE','RECEPTIONIST')
INSERT INTO Baseline_DB_Aug_2016.DBO.dept_desig  VALUES ('QA','MADDEN','ANALYST')
INSERT INTO Baseline_DB_Aug_2016.DBO.dept_desig  VALUES ('IT','NORRIS','TECHNICIAN')
INSERT INTO Baseline_DB_Aug_2016.DBO.dept_desig  VALUES ('ADMIN','PATRICK','CLERK')
INSERT INTO Baseline_DB_Aug_2016.DBO.dept_desig  VALUES ('DATA','SONJA','MANAGER')
INSERT INTO Baseline_DB_Aug_2016.DBO.dept_desig  VALUES ('QA','GEORGE','MANAGER')
INSERT INTO Baseline_DB_Aug_2016.DBO.dept_desig  VALUES ('ADMIN','EMILLY','MANAGER')
INSERT INTO Baseline_DB_Aug_2016.DBO.dept_desig  VALUES ('QA','PATRICK','TESTER')
INSERT INTO Baseline_DB_Aug_2016.DBO.dept_desig  VALUES ('DEVELOPMENT','ABDUL','MANAGER')
INSERT INTO Baseline_DB_Aug_2016.DBO.dept_desig  VALUES ('DATA','PATRICK','SUPERVISOR')
INSERT INTO Baseline_DB_Aug_2016.DBO.dept_desig  VALUES ('ADMIN','GEORGE','CLERK')
INSERT INTO Baseline_DB_Aug_2016.DBO.dept_desig  VALUES ('DEVELOPMENT','YURIY','SUPERVISOR')
INSERT INTO Baseline_DB_Aug_2016.DBO.dept_desig  VALUES ('DATA','GRAHAM','OPERATOR')

Now we have inserted all dummy records in the table, let's query the table to see what we have right now.

SELECT * FROM Baseline_DB_Aug_2016.DBO.dept_desig

ORDER BY dept_name

Query will show all 19 records order by dept_name as shown below, 

Arrows are showing top ranked employee for each  department, and this is our requirement, we need to get top most ranked employee for each department.

After running main script mentioned in script section result will look like below:

Explaination of code:

To understand above query lets divide the whole query in two parts 

  •  ?First is inner part, which is getting data as per our defined order for each department 
SELECT DEPT_NAME, EMP_NAME, DESIG, ROW_NUMBER() OVER

                          (PARTITION BY DEPT_NAME

                           ORDER BY CASE DESIG

                                         WHEN 'DIRECTOR' THEN 1

                                         WHEN 'MANAGER' THEN 2

                                         WHEN 'SUPERVISOR' THEN 3

                                         ELSE 4 END

                           ) AS SRLNO

 FROM DEPT_DESIG

this part of query will show below result, you can see the ranking in column SRLNO

  •  Second is outer part, which is refining inner data, and returning only top ranked (or SRLNO =1) for each department.

Conclusion :

OVER clause is very useful, especially when you are restricting your aggregate functions, you can get magical results by using OVER with RANKING Functions, Usually used with ORDER BY  to get Duplicate values and so on. 

Above query can be modified in inner part if your organization criteria is changed for employee ranking.

To change your required no. of employees you just can change the last part of query by changing criteria for WHERE clause, for example 

WHERE SRLNO in (1,2)

which gives you top 2 most ranked employees for each department

Total article views: 458 | Views in the last 30 days: 11
 
Related Articles
ARTICLE

Baseline Collector Solution V2

New version of the Free Baseline Collector Solution released. Do you collect baseline data? If not, ...

ARTICLE

Baselines

It can be hard to analyze performance without a baseline. This week, Steve Jones asks how you might ...

ARTICLE

Comprehensive Baseline Collector Solution (Updated)

There are no more excuses for not having baseline data. This article introduces a comprehensive Free...

ARTICLE

A Lightweight, Self-adjusting, Baseline-less Data Monitor

Can data alert us that something is going on, without baselines and thresholds?

ARTICLE

5 Reasons You Must Start Capturing Baseline Data

It is widely acknowledged within the SQL Server community that baselines represent valuable informa...

 
Contribute