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