SQL PRACTICE: SQL SERVER - Second MAX Salary

 Q6. How to find the employee with the second MAX salary using SQL?

N.B: I am using the following:

Database -> MSSQL

SQL Dialect -> T-SQL


Consider below employment table as a source data.

-- Create an employment table

create table employment(

EmpId int,

FirstName varchar(20),

LastName varchar(20),

Salary int

);

-- Insert values to the employment table

insert into employment(EmpId, FirstName, LastName, Salary)

Values

(100, 'Genet', 'Mathew', 40000),

(101, 'Mike', 'Mcarthy', 80000),

(102, 'Teddy', 'Arthur', 60000),

(103, 'Abrham', 'Zerai', 70000),

(104, 'Aman','Mike', 65000),

(105, 'Tedros','Haile', 50000),

(106, 'Abi', 'Jacob', 60000),

(107, 'Bruno', 'Yonus', 75000),

(108, 'Mathew', 'Samuel',60000),

(109, 'Rodas','Keyun', 65000),

(110, 'Mat', 'Joseph', 100000);

Select  * from employment;

Table 1. Employment Table



In order to find the second max salary from the given table, the employee record with max salary needs to be eliminated. This can be achieved in many ways. I will walk you through three ways here.

1.  Self Join Method

select max(salary) as Salary
from employment
where salary not in (select max(salary) as Salary
from employment)
The above query gives only the second MAX salary value. But, we need to fetch the entire employee records. To achieve this, we have to do self join on employment table based on salary value.

With cte as(
        select max(salary) as Salary
        from employment
        where salary not in (select max(salary) as Salary
       from employment)
    )
select e.* from employment e
join cte c on e.salary = c.salary;

Table 1. Self Join Method




2. Offset and Fetch Method

The offset and fetch clauses are used with order by clause as options. They are used to allow you to limit the number of rows to be returned by a query.

  • The Offset clause skips a number of rows before starting to return rows from a query. It is  mandatory and it has a parameter greater or equal to zero.
  • The Fetch clause specifies the number of rows to return after offset clause is processed. It has a parameter that is greater or equal to one.

select empid,
FirstName,
LastName,
salary
from employment
order by Salary desc
offset 1 row
fetch first 1 row only;

Table 2. Offset and Fetch Method

Explanation: The above query will first return the entire table sorted with the  max salary as the first row. To eliminate the first row, we will use offset. The offset clause will skip one row. Then, the fetch clause will return the first row after offset clause has been processed. Basically,  the query will return the second max salary from the sorted table. 


3. Dense_Rank Function

The dense_rank function calculates the rank of a row as a consecutive integers in an ordered set of rows starting from 1 and gives the same value for all the ties. 

select employment.*, 
            DENSE_RANK() over(order by salary desc) As Row_NUm
from employment;

                                                                  Table 3. Dense_Rank Function


With cte as(
select employment.*, DENSE_RANK() over(order by salary desc) As Row_NUm
from employment)
select * from cte
where Row_Num = 2;


                                                                         Table 4. Dense_Rank Function Result

Comments

Post a Comment

Popular posts from this blog

SQL PRACTICE: SQL SERVER - DATEADD Function

Image
  Q12. Find the join date of employees   if they  join after 2 weeks of the hire date? N.B: I am using the following: Database: MSSQL SQL Dialect: T-SQL  Consider below table (Sql_data) as a source data. select * from Sql_data; Table 1. Source Data -- Find the join date of employees select      empid,  FirstName,  LastName, DOB, Hire_date, DATEADD(WK, 2, Hire_date) as Join_date, Salary from Sql_data; Table 2. Join Date Explanation :  DATEADD  function adds a number to a datepart and returns a modified  value based on a   specified  datepart  (Year, Month, Week, Day …..etc...).  Syntax: DATEADD (datepart, NumberToAdd, Date) Parameters: datepart -  It is part of the date to which the dateadd () function will add a number. It can be Year(YY, YYYY), Quarter(QQ, Q), month(MM,M), day(DD, D), …..etc....  In our case, we are going to use Week (WK) NumberToAdd -  It is an ...
Read more

SQL PRACTICE: SQL SERVER - Unique Records

Image
 Q2: How to select unique values in SQL? N.B: I am using the following: Database: MSSQL SQL Dialect: T-SQL  Below is an employee table given. It has 3 fields and 7 records.                                                       Table1: Employee Table To find unique values of the table, I am going to use three different ways:  1. Using distinct clause select distinct EmpId, Name, Salary from employee; Table2: Distinct Clause Method 2. Using Group by (Without using distinct keyword) select EmpId, Name, Salary from employee group by EmpId, Name, Salary; Table3. Group By Method 3. Using Row_Number Function select Empid, Name, Salary , ROW_NUMBER() over(partition by empid, Name,  Salary order by empid) as Row_Num from employee; Result: Table4. Row_Number Function Row_number is used to give a unique number for each row as you can see from ta...
Read more

SQL PRACTICE: SQL SERVER: DATEDIFF Function

Image
 Q11. Find the age of employees from the provided table using SQL? N.B: I am using the following: Database: MSSQL SQL Dialect: T-SQL  Consider below table (Sql_data) as a source data. select * from Sql_data; Table 1. Source Data -- Find the age of employees select      empid,  FirstName,  LastName, DOB,  DATEDIFF(YY, DOB, GETDATE()) as Age from Sql_data;                                                                      Table 2. Age of Employees Explanation : DATEDIFF function is used to return the date difference between two dates according to the date part specified (Year, Month, Day …..etc...).  Syntax: DATEDIFF(datepart, Date1, Date2) Parameters: datepart - Used to get the time difference between the two dates. It can be Year(YY, YYYY), Quarter(QQ , Q)...
Read more