## 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.

Advertisements

Categories: Database, SQL Server

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