How do you find the Second highest Salary?
This is the most common question asked in Interviews.
EMPLOYEE table has fields EMP_ID and SALARY how do you find the second highest salary?
Answer:
We can write a sub-query to achieve the result
SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)
The first sub-query in the WHERE clause will return the MAX SALARY in the table, the main query SELECT’s the MAX SALARY from the results which doesn’t have the highest SALARY.
Categories: Database, SQL Server
Leave a reply to Arpit Parikh Cancel reply
Recent Comments
 | Nate on Stored Procedures are EVI… |
 | dhirendra kumar dhir… on How do you find the Second hig… |
| dhirendra kumar dhir… on How do you find the Second hig… |
 | shivani pandit on Indexes in SQL Server |
| shivani pandit on Difference between UNIQUE cons… |
MS SQL Server Tutorials 
I really liked this post. 🙂
hi………….
can u send querry , to get second highest salary from a table……..
select top 1 salary from (select distinct top 2 salary from employee desc order by salary) a order by salary
You can also use this way to find second highest salary in MySQL and SQ Server 2008
Excellent way to find that!
SELECT TOP 1 stud_name,stud_marks
FROM (SELECT TOP 2 stud_name, stud_marks FROM student ORDER BY stud_marks DESC) a
ORDER BY stud_marks ASC
Sir ,
Why you create object ‘a’ in query
AWESOME!
we should also use distinct in nested select to omit duplicate values
excellent
Please refer to following Sql Query it will work:
select top 1 * from salary where salary in(select top 2 salary from salary order by desc)
Kapil,
The post submitted by you will not work dear,
Reason: If your salar column having same value multiple time then this query will not show correct one,try this)
the post submitted by employee is ok.
Firstly I hate using the IN clause…. anyways the following query will work for…
a. if there are multiple Highest salary….
b. will list out names of all the second highest salary ….
select name, salary from emp where salary =
(select max(salary) from emp where salary
Select Top 1 salary from emp where salary
Not IN(select Top 1 salary from emp order by salary Desc)order by salary Desc
Correct query .. thanks dude …
select max(sal) from employee where sal
select max(sal) from emp
where sal
select max(emp_sal) from salary where emp_sal not in ( select top(1) emp_sal
from salary order by emp_sal desc)
Hai alll
i want query for 3rd highest salary in a table
please guide me my mail id
marni.ravikiran@hotmail.com
thank u
ravi
select max(salary) from emp_salary
where salary not in (select top 2 salary from emp_salary order by salary desc)
select *from emp e where 2=(select count(distinct sal) from emp m where m.sal>e.sal)
this will work perfectly try it.
here put ‘0’ — 1st highest sal
‘1’—2nd highest sal…..etc
Hi Ravindra,
This was the best of the lot.
It also works even in SQL 2000.
I just reordered your query as an SP
–QUERY FOR Nth MAXIMUM SALARY RECORD
–Rank 0 is first rank, 1 is second rank, 2 is third rank
CREATE PROCEDURE NthMaxRecord (@position int)
AS
SELECT * FROM EMP E WHERE (@position – 1) = (SELECT COUNT(DISTINCT BASIC) FROM EMP M WHERE M.BASIC>E.BASIC)
–EXECUTING THE STORED PROCEDURE (HERE FOR 3rd RANK)
EXEC NthMaxRecord 3
Thanks once again Ravindra
– Joe
Hi ALL,
can any one tell me to find out the “nth highest salary”?
thanx in advance.
KMR
select *from emp e where 2=(select count(distinct sal) from emp m where m.sal>e.sal)
this will work perfectly try it.
here put ’0′ — 1st highest sal
’1′—2nd highest sal…..etc
HI ravi,your Query working correctly. can you explain how it works?
Hi Ravi try 4 this u may get…
SELECT [Employee_ID],
[number],
[name],
[salary]
FROM [Employee]
WHERE [salary] = (
SELECT MIN( [salary] )
FROM (
SELECT DISTINCT TOP 3 [salary]
FROM [Employee]
ORDER BY [salary] DESC
) [FourHighestDistinctSalaries]
)
can you tell me how to get 3rd highest salary from the table emp
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 3 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
How do you find the third highest Salary?
i have a complete post related to this, in this article i have posted quries for both SQl Server 2000 and 2005 http://hemantg.blogspot.com/2007/06/retrieving-nth-salary-in-sql-server.html
Thanks for this better solution
If you want employee greatest salary ( 1st or 2nd or 3rd …. any thing ,in place of “N” in query, place the number)
SELECT * FROM emptable e1 WHERE (N = (SELECT COUNT(DISTINCT (e2.empsalary))
FROM emptable e2 WHERE e2.empsalary >= e1.empsalary))
top 5th max salary of a employee in a employee table
SELECT * FROM employee e1 WHERE (5 = (SELECT COUNT(DISTINCT (e2.emp_salary))
FROM employee e2 WHERE e2.emp_salary >= e1.emp_salary))
SELECT max(Salary)FROM Emp WHERE Salary <select MAX(Salary) from Emp
select min(sal) from emp where (sal) IN (select top2 sal from emp);
SELECT * FROM emptable e1 WHERE (N = (SELECT COUNT(DISTINCT (e2.empsalary))
FROM emptable e2 WHERE e2.empsalary >= e1.empsalary))
can any one explain this query whats the e1 & e2 tables
How do you find the 4th highest Salary in sql server without using subquery? pls reply
select min(sal) from(select sal from tablename order by sal desc)
where rownum<=4;
select ename,sal from(select ename,sal from emp
order by sal desc
where rownum<=3
pls reply.
How do you find the 4th highest Salary in sql server without using subquery?
Select Top 1 sal from employee where sal
Not IN(select Top 3 sal from employee order by sal Desc)order by sal Desc
How do you find the 6th highest Salary in sql server & oracle 10g without using subquery?
3rd highest salary in a table
Select Top 1 salary from empsal where salary
Not IN(select Top 2 salary from empsal order by salary Desc)order by salary Desc
i wanted to know the second highest salary
and second highest salary in two table
TO FIND THE Nth HIGHEST SALARY
SELECT MIN(SALARY) FROM (SELECT DISTINCT SALARY FROM EMPLOYEE ORDER BY SALARY DESC)WHERE ROWNUM=5;
SELECT MIN(SALARY) FROM (SELECT DISTINCT SALARY FROM EMPLOYEE ORDER BY SALARY DESC)WHERE ROWNUM=N;
I WANT TO CONNECT THE MULTIPLE USERS IN ORACLE1Og
WHTS THE PROCESS
How to find highest 2 salary from a table
select Distinct Top 2 Salary from EMP order by Salary desc
Thanks Dear..
It is really helpful to complete my requirement…
this is good site for us for search any question for sql as well as orecle thenks .
SELECT * FROM temp_emp T1 WHERE
4 = (SELECT COUNT(DISTINCT(T2.salary)) FROM temp_emp T2 WHERE T2.salary >= T1.salary);
this site is good.
how to join two different tables.
Nice one. This post contains all the possible options to write the query.
nice
Too good. Thanks everyone for sharing the knowledge.
ravi’s post rocks.
Thanks Alot
really the query helps…
grt man………..
see this website
how two find the second maximum number by using sqlserver without sub query
select min(sal) from emp where sal in(select distinct top 2 sal from emp order by sal desc)
1. select max(esal) from emp where esal not in(select max(esal) from emp
2.select esal from emp e1 where 2=(select count(distinct esal) from emp e2 where e2.esal>=e1.esal)
3.select top2 * from emp where esal not in(select max(esal) from emp)
select top 1 * from salary where salary in(select top 2 salary from salary order by desc)
is not working
select distinct sal from employee t
where 3 = (select count(distinct sal)
from employee
where t.sal<=sal)
I v have more than 10 equal and highest salary employee
Select Top 1 salary from salary where salary
Not IN(select Top 1 salary from salary order by salary Desc)order by salary Desc
select max(salary) from salary where salary not in ( select Top 1 salary from salary order by salary desc)
select trk_id from csl_trk a where 3=(select count (distinct(b.trk_id))from csl_trk b where a.trk_id<=b.trk_id);
how to find second highest salary in postgreSQL.
select distinct(Salary),* from Employee A where 3=(select count(distinct(Salary)) from Employee b where b.Salary <= a.Salary)
nice
only one
select top 1 salary from employee where salary<(select max(salary) from
employee) order by salary desc
it will work for all condition for ms sql server 2005
to find the second highest salary ……….
SURENDRA GURJAR
Here N is any number(you just put that number here and find that number of highest salary)
This query takes data from two tables
1.EMPLOYEE
2.SALARY_DETAILS
SELECT TOP 1 * FROM (SELECT TOP N * FROM (SELECT EMPLOYEE.FNAME,SALARY_DETAILS.SALARY FROM EMPLOYEE INNER JOIN SALARY_DETAILS ON EMPLOYEE.ID = SALARY_DETAILS.ID) AS D ORDER BY SALARY DESC) AS P ORDER BY SALARY
I want to get the employee name as well for the second highest salary getter, if I do
SELECT ENAME, MAX(SAL)
FROM EMP
WHERE SAL < (SELECT MAX(SAL) FROM EMP)
or
SELECT ENAME, MAX(SAL) AS SAL
FROM EMP
WHERE SAL NOT IN (SELECT MAX(SAL) FROM EMP)
then an error message says
“Column ’emp.ENAME’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.”
Plz provide a solution
I donot want to use the TOP Function
Regards
Mahfooz
select ename,sal from emp where sal=(
select max(sal)
from emp
where sal<(select max(sal) from emp) )
Thanks,
-Rajesh
How to find the second max salary for each deportments for the below examples.
Table Name is EMP:
Name Age Sal Dept
——————————————————
AA 15 1000 10
BB 20 3500 20
CC 18 3000 10
DD 21 2500 10
EE 19 3000 20
Expected Output is,
Sal Dept
—————————-
3000 10
3500 20
Please share your valuable information.
That’s a bit tricky ……. I just lost 10 marks due to this ……….. ::(
How to find second highest value of a column in a table? – SQL …
Declare @temp int
set @temp = 2
SELECT MAX(SALARY) from EMPLOYEE_PAY_TBL WHERE SALARY NOT IN ( SELECT TOP (@temp – 1) SALARY FROM EMPLOYEE_PAY_TBL ORDER BY SALARY DESC)
Gud Site
Please mention Oracle Version for usage of TOP keyword.
Select emp_sal from salary where emp_sal not IN (Select max(emp_sal) from salary) ORDER BY emp_sal DESC LIMIT 0 , 1
Excellent code
how 2 find the top 5 salaries of employees in the employee table.
how 2 enter one system from another system when they r connected in network,and how 2 trace the second system password.
select * from employee orderby salary DESC
Limit 1,1
My answer for same questions
SELECT SALARY FROM EMPLOYEE WHERE Order by SALARY Desc limit 1,1.
select max(sal) from emp where sal not in (select top 1 sal from emp order by sal desc)
select a.sal
from emp a
where 2 = (
select count(distinct b.sal)
from emp b
where b.sal >= a.sal
)
order by a.sal desc;
select max(sal)
from emp
where sal not in (select max(sal) from emp)
very gud ans
select min(PaymentAmount) from
(select distinct top 7 t1.PaymentAmount from dbo.MonthlyPayments t1 order by t1.PaymentAmount desc) a
hi, this is very nice
it was very usefull to me
thanks
select max(sal) from EMP
where sal< (select max(sal) from EMP)
Second minimum sal in EMP table
select min(sal) from EMP
where sal > (select min(sal) from EMP)
SELECT * FROM emptable e1 WHERE (N = (SELECT COUNT(DISTINCT (e2.empsalary))
FROM emptable e2 WHERE e2.empsalary >= e1.empsalary))
please explain this query
I tried this query.. it works fine…
SELECT EmpName, Salary
from
(
SELECT EmpName, Salary, Row_Number() OVER(ORDER BY SALARY DESC) AS ‘Salaries’
FROM #Employees
) emp
WHERE Salaries = n
n may be 2,3….
Please send how to code to find the highest record from a field while the number is added with any string.
for example “BIS001,BIS002,BIS009,BIS010,BIS003″…i need ans hear is BIS010
we can use this query to display all the records regardinh second highest salary
select * from emp
where sal=(select max(sal) from emp where sal<(select max(sal) from emp));
use this it will work
SELECT MAX(sal) FROM emp WHERE sal < (SELECT MAX(sal) FROM emp)
this was super….. my frd… thanks for this
thank u for ur positive response
i will contact when i have another problem
Ok have a good
Empsal table data
700
500
100
900
400
200
600
750
query to find second highest salary from table Empsal
mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary<(select max(salary) from Empsal));
Output=750
forget nthe book logic
query to find third highest salary from table Empsal
mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary(select max(e1.salary) from Empsal e1
>where e1.salary IN(
>select e2.salary from Empsal e2 where
>e2.salary<(select max(salary) from Empsal))));
Output=700
amitsing2008@gmail.com(amy is back)
hi I find out dob january releate all emloyee
and my column filed is id,name dob
how to find dob of only january employee find out whatever any years .
so ,plz help me sir
select name, dob from employee where dob= ‘0000-01-00 ‘
this type we try to find out .
SELECT MAX(sal) AS Expr1 FROM emp WHERE (sal NOT IN
(SELECT MAX(sal) AS Expr1 FROM emp AS expr2))
Excellent Answer
how t print this
1
2
three
.
.
10
11
..1three
…2three
three three
simpel
Here you will get second highest and lowest value from the record table
Second Highest:
SELECT * FROM empsalry s WHERE 1 = (SELECT COUNT(DISTINCT salary) FROM empsalry s1 WHERE s.salary s1.salary);
nth lowest salary
SELECT * FROM empsalry s WHERE n-1 =
(SELECT COUNT(DISTINCT salary) FROM empsalry s1 WHERE s.salary > s1.salary);
sorry for previous post correct is here:
second highest:
SELECT * FROM empsalry s WHERE 1 = (SELECT COUNT(DISTINCT salary) FROM empsalry s1 WHERE s.salary s1.salary);
lowest salary
SELECT * FROM empsalry s WHERE n-1 =
(SELECT COUNT(DISTINCT salary) FROM empsalry s1 WHERE s.salary > s1.salary);
Here something is wrong in this blog i am trying to put symbol, but not able to show it on post. I don’t know what is the problem so plese s.salary < s1.salary in first query
Hi Friend’s i am Gaurav Badhani, and i think this querry will work for 2nd highest salary if u want try this and for more help give me a querry in badhani_gaurav@yahoo.com
SELECT max(Salary)FROM Emp WHERE Salary <select MAX(Salary) from Emp
thats greate and simple way. i thought it would be very complex.
thanx
Thank u so much friend ….
please give me a suggestion for second highest salary in sql
Hi all,
Find the ‘ORDER BY’ and ‘GROUP BY’ in sql for me.
SELECT *
FROM `table name`
ORDER BY `salary` DESC
LIMIT 1 , 1
Hi all,
find out the ‘magic methods’.
Why are you people making it complicated… I will give one easy query to find n highest salary…
select min(salary) from (select distinct top n salary from emp order by salary desc)
where n = 2,3,… (2=second highest salary, 3=third highest salary,…)
small change…
select min(salary) from emp where salary in (select distinct top n salary from emp order by salary desc)
select min(salary) from emp where salary in (select distinct top n salary from emp order by salary desc)
thanks a lot
Really neat and simple solution…
Thanks..
Nice!
SELECT *
FROM `test`
ORDER BY `last_name` DESC
LIMIT 1 , 1
GO for it
SELECT *
FROM `table name`
ORDER BY `salary` DESC
LIMIT 1 , 1
GO for it
+
how can we get last five record from table
It will return top second highest salary:
SELECT MAX(SALARY) FROM
(SELECT TOP (2) emp.SALARY FROM EMPLOYEE emp)Q1
SELECT TOP 2
MAX SALARY AS [MAXIMUM SALARIES]
FROM YOUR_TABLE
ORDER BY SALARY DESC
The following query will work for nth highest salary.
SELECT EName,salary FROM Employee A
Where n = (select Count (distinct(salary)) From Employee B
WHERE B.salary>=a.salary)
In the above query, replace n with the number you want.
xcellent, realy xcellent.
SELECT MIN(Sal) FROM TableName
WHERE Sal IN
(SELECT TOP 2 Sal FROM TableName ORDER BY Sal DESC)
SELECT SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC
LIMIT 1 , 1
limit 1.1 means second result to second result . minimum limit is zero. for second result limit 1,1 and remember one thing descending order is used
this one is good
ya this one
Simple Solution>>>>>>>>>>
select & from table t1 where 2=(select count(distinct age) from table t2 where t2.age>t1.age);
select * from table t1 where 2=(select count(distinct age) from table t2 where t2.age>t1.age);
SELECT MAX(SALARY) FROM mgm WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM mgm) From this we can only find second salary.. but for others like fifth forth tenth etc salaries this method is not good I thing. Check with this query. May this will help you.
CREATE TABLE mgm
(name varchar(20),no int,addr varchar(20),dept varchar(20),country varchar(20),salary int)
INSERT INTO mgm values(‘af’,3,’egh’,’erg’,’sder’,656)
INSERT INTO mgm values(‘fgdg’,2,’sg’,’sg’,’sderty’,567)
INSERT INTO mgm values(‘afdg’,4,’sgfdsg’,’fdgh’,’ety’,678)
INSERT INTO mgm values(‘dfghdh’,5,’sgf’,’tey’,’reyt’,4565)
INSERT INTO mgm values(‘dgh’,6,’fr’,’rttr’,’sdhyj’,5467)
INSERT INTO mgm values(‘dshgf’,7,’ttyhyh’,’hhh’,’sd’,4767)
select name,no,addr,dept,country,salary from
(select dense_rank() over (order by salary desc) as ranking,name,no,addr,dept,country,salary from mgm
)a
where a.ranking in (1,2,3)
price;
?>
select price FROM sub_price ORDER BY price desc LIMIT 1, 2
SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)……will give 2nd highest salary
how to get the nth highest salary????
SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)….will give the 2nd highest salary
how to get the nth highest salary???????
gud evng all ,,,
when i use TOP in query,, then show ,,” Invalid Identifier ”
pls tell me the reason
Select salary from Employee order by salary desc limit 1,1
For retrieving the second highest salary from the Employee table.
TO SELECT SECOND MAX SALARY
select max(salary)from table_name where salary<(select max(salary) from table_name);
SELECT DISTINCT SAL FROM EMPLOYEE ORDER BY SAL ASC LIMIT 1 , 1
OR
SELECT SAL FROM EMPLOYEE GROUP BY (SAL) ORDER BY SAL ASC LIMIT 1 , 1
thanks
to find 2nd highest salary
select max(sal) from emp where sal < ( select max(sal) from emp);
to find 3rd highest salary
select max(sal) from emp where sal < ( select max(sal) from emp where sal < ( select max(sal) from emp) )
you can do it by below query as well
suppose i have to find out 5th highest salary of the employee
select Salary from
(select *,DENSE_RANK() over (order by Salary desc) as ‘num’ from tblEmployee) t1
where num=5
it will return the fifth highest salary of the employee
Sir we have table named XYZ having colums name,address ,city
now i want to find all the records of satellite(area) in the city ahmedabad..
select max(salary) from emp where salary<(select max(salary) from emp)
SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE)
“select emp_salary from emp_salary_master order by emp_salary desc limit 2,1”
-Query for 3rd highest salary
Hi,
I have 10 salary record in a table. I want to retrieve 3rd to 5th highest salary from the tbl.
eg. tableA
10000,
9000,
8000,
7000, out put should be = 8000,
6000, 7000,
5000, 6000
3000,
2000,
1000
please help with query in SQL server.?
Hi,
I have 10 salary record in a table. I want to retrieve 3rd to 5th(means three record) highest salary from the tbl.
eg. tableA
10000,
9000,
8000,
7000,
6000,
5000,
3000,
2000,
1000
out put should be three record list = 8000, 7000,6000
please help with query in SQL server.?