How to find 2nd Highest Salary | Find Nth Salary | 4 ways to get it

 In This tutorial we will discuss about how to get 2nd highest salary in employee table. Below are the different ways:

EmployeeDetail Table:

Using sub-query (With < Operator):

First, we find the employee with highest salary. To do this we run this query:

Select max(Salary) from EmployeeDetail

This will give us the Maximum Salary; we can further nest this query to a subquery to find the Second Highest Salary. This query will work on MYSQL, ORACLE as well as SQL Server :

Using sub-query (With Not In Operator): 

We can also use the NOT IN Clause instead of comparing the salary between two sets.

Using correlated sub-query:

The distinct keyword is there to deal with duplicate salaries in the table. In order to find the Nth highest salary, we are only considering unique salaries. The highest salary means no salary is higher than it, the Second highest means only one salary is higher than it, 3rd highest means two salaries are higher than it, similarly Nth highest salary means N-1 salaries are higher than it.

For each record processed by outer query, inner query will be executed and will return how many records has records has salary less than the current salary. If you are looking for second highest salary then your query will stop as soon as inner query will return 2.

Using TOP Clause keyword:

We can use TOP Command in SQL Server. The sub-query produces a result set containing the salary of employees arranged in decreasing order and fetches TOP 2 records from the set; we use DISTINCT (can be ignored) to segregate duplicate values (if any). Then from RESULT of sub-query, we order the two rows in Ascending order the get the Topmost row which gives us the 2nd Highest Salary.

Please find below all the queries as per above method:

Using sub-query (With < Operator):

2nd Highest Salary
Select max(Salary) As Salary from EmployeeDetail where 
Salary < (Select max(Salary) from EmployeeDetail

3rd Highest Salary
Select max(Salary) As Salary from EmployeeDetail where 
Salary < (Select max(Salary) from EmployeeDetail Where
Salary < (Select max(Salary) from EmployeeDetail))

Using sub-query (With Not In Operator)
Select max(Salary) As Salary from EmployeeDetail where 
Salary NOT IN (Select max(Salary) from EmployeeDetail)


Using correlated sub-query
Select distinct Salary from EmployeeDetail A
Where 2=(Select count(distinct salary) from EmployeeDetail B where
A.Salary <= b.Salary)


Using TOP Clause keyword
Select top 1 Salary From
(Select distinct top 2 Salary from 
EmployeeDetail Order by Salary Desc)
As Temp Order by Salary 

For detail explanation please watch below YouTube video:

SQL Aggregate Functions and Group By Clause

 In this post we will discuss below points:

• SQL Aggregate Functions
• Explanation of each with Example
• Group By Clause
• 10 SQL Queries

Aggregate Function

SQL aggregation function is used to perform the calculations on multiple rows of a single column of a table. It returns a single value. 

Count()

COUNT function is used to Count the number of rows in a database table. It can work on both numeric and non-numeric data types.

Count(*): Returns total number of records .Count(salary): Return number of Non Null values over the column salary.

Sum()

Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.

Example: Select SUM(Salary) from EmployeeDetail.

Avg()

The AVG function is used to calculate the average value of the numeric type. AVG function returns the average of all non-Null values.

Example: Select AVG(Salary) from EmployeeDetail.

Max()

MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.

Example: Select MAX(Salary) from EmployeeDetail.

Max()

MAX function is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.

Example: Select MAX(Salary) from EmployeeDetail.

Min()

MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column.

Example: Select MIN(Salary) from EmployeeDetail.

Group By Keyword

The GROUP BY clause is a SQL command that is used to group rows that have the same values. The GROUP BY clause is used in the SELECT statement.  

The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.

Example: SELECT column1, column2

      FROM table_name

      WHERE [ conditions ]

      GROUP BY column1, column2

      ORDER BY column1, column2

Sample Queries:

Get the highest "Salary" from EmployeeDetail table. 
SELECT MAX(Salary) As MaxSalary FROM [EmployeeDetail]

Get the lowest "Salary" from EmployeeDetail table.
SELECT MIN(Salary) As MinSalary FROM [EmployeeDetail]
 
Get how many employee exist in "EmployeeDetail" table.
SELECT COUNT(*) FROM [EmployeeDetail]

Write the query to get the department and department wise total(sum) salary from "EmployeeDetail" table. 
SELECT Department, SUM(Salary) AS [Total Salary] FROM [EmployeeDetail] GROUP BY Department

Write the query to get the department and department wise total(sum) salary, display it in ascending order according to salary.
SELECT Department, SUM(Salary) AS [Total Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY SUM(Salary) ASC

Write the query to get the department and department wise total(sum) salary, display it in descending order according to salary.
SELECT Department, SUM(Salary) AS [Total Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY SUM(Salary) DESC
 
Write the query to get the department, total no. of emp in each department, total(sum) salary with respect to department from "EmployeeDetail" table. 
SELECT Department, COUNT(*) AS [Dept Counts], SUM(Salary) AS [Total Salary] FROM [EmployeeDetail] GROUP BY Department

Get department wise average salary from "EmployeeDetail" table order by salary ascending.
SELECT Department, AVG(Salary) AS [Average Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY AVG(Salary) ASC

Get department wise maximum salary from "EmployeeDetail" table order by salary ascending.
SELECT Department, MAX(Salary) AS [Average Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY MAX(Salary) ASC
   
Get department wise minimum salary from "EmployeeDetail" table order by salary ascending.
SELECT Department, MIN(Salary) AS [Average Salary] FROM [EmployeeDetail] GROUP BY Department ORDER BY MIN(Salary) ASC

Please refer below YouTube video for more detail explanation:

SQL Queries on SQL Like, SQL Wildcards, RTRIM() and LTRIM() functions

• Select all employee detail with First name "Tom" and "Lucy"

Answer: Select * from EmployeeDetail Where FirstName IN ('Tom','Lucy') 

• Select all employee detail with First name Not  "Frank" and "Jane” 

Answer: Select * from EmployeeDetail Where FirstName NOT IN ('Frank','Jane')

• Select first name from "EmployeeDetail" table after removing white spaces from right side 

Answer: Select RTRIM(FirstName) AS [First Name] From EmployeeDetail 

• Select first name from "EmployeeDetail" table after removing white spaces from left side 

Answer: Select LTRIM(FirstName) AS [First Name] From EmployeeDetail

Where EmpId=13 

• Select first name from "EmployeeDetail" table prefixed with "Hi

Answer: Select 'Hi '+FirstName from EmployeeDetail 

• Get employee details from "EmployeeDetail" table whose Salary greater than 70000 

Answer: Select * from EmployeeDetail where Salary>70000

• Get employee details from "EmployeeDetail" table whose Salary less than 70000 

Answer: Select * from EmployeeDetail where Salary<70000

• Get employee details from "EmployeeDetail" table whose Salary between 50000 than 60000
  

Answer: Select * from EmployeeDetail Where Salary Between 40000 AND 60000

Please refer below YouTube video for more detail explanation:

Some More SQL Queries on Wildcard, Distinct etc

•  Get all employee detail from Employee table whose "FirstName" not start with any single character between 'a-p' 

Answer:  SELECT * FROM [Employee] WHERE FirstName like '[^a-p]%'

• Get all employee detail from Employee table whose "Gender" end with 'le' and contain 4 letters. 

Answer:  --The Underscore(_) Wildcard Character represents any single character. 

SELECT * FROM [EmployeeDetail] WHERE Gender like '__le' --there are two "_"

• Get all employee detail from Employee table whose "FirstName" start with 'F' and contain 5 letters. 

Answer: SELECT * FROM [EmployeeDetail] WHERE FirstName like 'F____' --there are four “_”

• Get all employee detail from Employee table whose "FirstName" containing '%'. ex:-"Fra%nk".

Answer: SELECT * FROM [EmployeeDetail] WHERE FirstName like '%[%]%' 

--According to our table it would return 0 rows, because no name containg '%

DISTINCT keyword - The SELECT DISTINCT statement is used to return only distinct (different) values.

The SQL DISTINCT keyword is used in conjunction with the SELECT statement to eliminate all the duplicate records and fetching only unique records.

• Get all unique "Department" from Employee table. 

Answer: SELECT DISTINCT(Department) FROM EmployeeDetail

Please refer below YouTube video for more detail explanation:

SQL Order By, Where Clause, Like Operator and SQL Wildcards

 In this post we will discuss about:

• SQL Order By
• Where Clause
• SQL Like
• SQL Wildcards
• Problems and write SQL queries

Order By 

Order By - The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default.

Where Clause

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition. SQL requires single quotes around text values. However, numeric fields should not be enclosed in quotes.

The following operators can be used in the WHERE clause: =, >, <, >=, <=, <>, BETWEEN, LIKE, IN 

SQL Wildcards

A wildcard character is used to substitute one or more characters in a string.

Wildcard characters are used with the LIKE operator. 

SQL Like 

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

%  - Represents zero or more characters

Example: Hi% finds Hitendra, Hit, Hi

_   -Represents a single character

Example: V_rma find Verma, varma

[] - Represents any single character within the brackets

Example: h[oa]t finds hot and hat, but not hit

^  - Represents any character not in the brackets

h[^oa]t finds hit, but not hot and hat

- Represents a range of characters

Example: c[a-b]t finds cat and cbt

Queries to Solve

• List the employees in the asc order of their Salaries? 

Answer: Select * from EmployeeDetail Order By Salary;

• List the employees in the desc order of their Salaries? 

Answer: Select * from EmployeeDetail Order By Salary DESC;

• List the details of the emps in asc order of the FirstName and desc of Department? 

Answer: SELECT * from EmployeeDetail Order BY Firstname ASC, Department DESC;

• Select employee detail whose name is "Jane" 

Answer: Select * from EmployeeDetail where FirstName='Jane'

• Get all employee detail from Employee table whose "FirstName" start with letter 'r'. 

Answer: Select * from EmployeeDetail where FirstName like 'R%'

• Get all employee details from Employee table whose "FirstName" contains 'a' 

Answer: Select * from EmployeeDetail where FirstName like '%a%'

• Get all employee details from EmployeeDetail table whose "FirstName" end with 'm' 

Answer: Select * from EmployeeDetail where FirstName like '%m'

• Get all employee detail from EmployeeDetail table whose "FirstName" start with any single character between 'a-p' 

Answer: Select * from EmployeeDetail where FirstName like '[a-p]%'

Please refer below YouTube video for more detail explanation: