Oracle find first element in a group

Overview

This is one of the common problems I have faced while writting Oracle queries. Data in relational tables is to be grouped together based on the criteria and top element of each group is to be printed out. In this article I have defined different ways of doing this by taking a simple example.

Example

Employee table contains Employee name, department and their salaries. As part of SQL query we need to list down top paid employee from each department. 

 

Table structure

Empid
Name
Department
Salary
1 Rich Manufacturing 1200
2 Dave Manufacturing 1400
3 Harry Marketing 1800
4 Larry Marketing 1700
5 Tom Manufacturing 1000

 

Option 1 – Rank function

This is the most preferred approach to use Oracle’s built in rank() function. This function can be used to rank each row/record with a group of values and then elements can be picked up having particular rank. For our employees problem statement described above, employees can be ranked in each dept based on their salaries and then employees with rank = 1 can be printed. 

Query

select * from (

    select empid, name,

    department, salary,

    rank() over (partition by department order by salary desc) as rnk 

    from Employee 

) where rnk = 1

 

 

The above query will print below two records – 

Empid
Name
Department
Salary
Rnk
2 Dave Manufacturing 1400 1
3 Harry Marketing 1800 1

 

Similarly rank() function can be used for other criterias within a group. For above example, rank function can be used to print employees from each department with least salary or top 2 paid employees from each department. 

Other examples

Employees with least salary in each department

select * from (

    select empid, name,

    department, salary,

    rank() over (partition by department order by salary) as rnk 

    from Employee 

) where rnk = 1

 

Top 2 paid employees in each department

select * from (

    select empid, name, department, salary, rank() over (partition by department order by salary desc) as rnk 

    from Employee 

) where rnk < 3

 

Option 2 – Using Nested Queries

This is not an elegant way but can be used in case somebody doesn’t want to use rank() function or the requirement is very straight forward. In this method, we can query over each record in the table and simulataneously check if their condition (salary in this case) matches with the top salary in their respective department. 

Query 

select emp.* from Employee emp 

where emp.salary = (select max(ee.salary)

                                 from Employee ee

                                where emp.department = ee.department) 

 

As you see this option can’t be easily used for other conditions if we need to find top 2 or top N paid employees from each department. 


Leave a Reply

Your email address will not be published. Required fields are marked *