How To Find The Maximum Salary In Sql

What is the Query to Observe 2nd Highest Salary of Employee?

Query to Notice 2nd Highest Salary Of Employee ane of the most commonly asked question in SQL interviews:

Answer:

select distinct bacon from Empoyee e1 where two = (select count(singled-out salary) from Employee e2 where e1.salary <= e2.salary);

CLICK HERE TO GET twenty COMPLEX SQL QUERIES IMPORTANT FOR INTERVIEW

Explanation of Query for case :

1.Distinct keyword:–>The Distinct keyword is very useful keyword which volition eliminate duplicates and fetches the Outcome.

2.Aliases–> Alias is aught but the obfuscation technique to protect the real names of database fields

3.Count()–>Count() role is used to count the records in the tabular array.

Pace 1: Then to understand the above query we will start with simple select statement. for Example,

Select * from Employee;

Simultaneously you can meet example of select statement here

Output:

Employee_num	Employee_name	Department	Salary
1	Amit	OBIEE	680000
two	Rohan	OBIEE	550000
3	Rohit	OBIEE	430000

Here our intention is to fetch the tape where bacon is 2d highest.

Step 2 :We volition fetch the distinct Salary of employee and give the allonym to it. For instance ,

Select distinct Salary from Employee e1;

Output:

Salary

680000

550000

430000

Footstep three :We demand to summate 2nd highest bacon.So we need to get the count of all distinct salaries. To illustrate this ,

Select count(singled-out Bacon) from Employee e2;

Output:

Stride four :Nosotros actually need to calculate 2nd highest salary.And then we will change the Pace two query and Step3 Query: Below is example,

Select distinct Salary from Employee e1 where 2=

Select count(singled-out Bacon) from Employee e2 where e1.bacon<=e2.salary;

Output:

The in a higher place query volition requite united states of america the 2nd highest bacon of employee.Promise you will understand the logic behind fetching the second highest bacon.At that place are other ways to find 2nd highest salary of employee using rank and dense_rank office.

Other means to observe second highest salary:

Query 1:

SELECT max(e1.sal), e1.deptno FROM s_emp e1 WHERE sal < (SELECT max(sal) FROM s_emp e2 WHERE e2.deptno = e1.deptno) GROUP BY e1.deptno;

Query two:

SELECT * FROM (SELECT Due south.*,DENSE_RANK() OVER (PARTITION BY DNO Guild By SALARY DESC) DR FROM SOURCE ) S WHERE S.DR=two;

Query three:

Select Name From Employees Where Salary =

(Select Distinct Pinnacle(1) Salary from Employees where Salary Not In

(Select Dustinct Top(ane) Bacon from Employees Order By Salary Descending) Guild past Salary Descending);

There are some other means of calculating the second highest salary in different DBMS i.eastward. Oracle,Mysql,Postgresql:

Type 1 : Using correlated subquery:

SELECT name, salary FROM Employee e1 WHERE N-ane = (SELECT COUNT(Distinct salary) FROM Employee e2 WHERE e2.salary > e1.salary)SELECT name, salary FROM Employee e1 WHERE 2-i = (SELECT COUNT(Singled-out salary) FROM #Employee e2 WHERE e2.salary > e1.bacon);

Using this method of finding out the second highest salary is deadening considering of the execution of inner query again and once again.

Type two: Microsoft SQL Server (Using Peak Part)

SELECT Top 1 bacon FROM ( SELECT Distinct TOP N salary FROM Employee ORDER Past salary DESC ) Equally temp Social club By salary;

Type3 : Mysql (using limit Clause)

SELECT salary FROM Employee ORDER Past salary DESC LIMIT 1,1;

Type 4: Oracle Function (using rownum function)

SELECT Salary FROM ( SELECT due east.Salary, ROW_NUMBER() OVER (Club BY bacon DESC) rn FROM Employee e ) WHERE rn = 2;

Type 5 : One of the website reader has suggested this solution Solution of Gautam Roy :

select * from OrderDetails a where a.Quantity < (select max(b.Quantity) from OrderDetails b) order by a.Quantity DESC limit 1;

Type 6 :However another Reader equally given the below as to illustrate,

name sal
———- ———————
x 120.00
y 140.00
z 200.00
p 400.00

select max(sal) as maxsalary from bacon where sal < ( select max(sal) equally maxsalary from salary );

Output :

maxsalary

——————

200.00

You tin can try this also for getting results easy accordingly,

2nd highest salary

Type vii : Suggested by ane of the Reader Selva Saleen for case :

select salary from (select rownum row_num,salary from (select * from EMPLOYEE E order by East.bacon desc))where row_num=two;

Type viii : Notice Nth Highest Salary using Limit Keyword Suggested by Reader named Dinesh Singh for example :

Select proper name, salary from employee social club by bacon desc limit(ane,due north-1);

Type 9 :The next New Solution suggested past reader named Victor for case :

select distinct e2.bacon
from (select A.Employee_num , A.salary , count(singled-out B.salary) as salary_distinct_rank
from Employee A
left join
Utilize B
where B.salary <= A.salary
group by A.Employee_num, A.salary ) e2
where e2.salary_distinct_rank = ii ;

Type x : New Solution suggested by reader named Nikhita:

Select Meridian 1 Salary from Employee where Bacon<(Select max(Salary) from Employee);

These are higher up methods to calculate 2nd highest salary of Employee.If y'all like this article then don't forget to share it.

CLICK HERE TO GET INFORMATION About TECH MAHINDRA INTERVIEW QUESTIONS

I hope you like this commodity and the commodity is useful to find second highest bacon in multiple means. If yous like this article or if you have whatsoever problems with the same kindly annotate in comments section.