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.
I really liked this post.
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
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
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
Hi ALL,
can any one tell me to find out the “nth highest salary”?
thanx in advance.
KMR
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]
)
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
pls reply.
How do you find the 4th highest Salary in sql server 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
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
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.
Too good. Thanks everyone for sharing the knowledge.
ravi’s post rocks.
Thanks Alot
really the query helps…
grt man………..
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
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.