how to find the 2nd higgest salary in the column for example
take table name is employee?
And also find bottom 2nd lowest salary ?
- 11 Answers
- 13276 Views
- GeoTechnology, Wipro, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / arrry.net
SELECT * FROM employee
WHERE Salary=(SELECT max(Salary)
FROM employee WHERE Marks< (SELECT max(Salary) FROM
employee));
| Is This Answer Correct ? | 4 Yes | 2 No |
Answer / arrry.net
Select top 1 salary from tbl_registration where salary in
(SELECT TOP 2 salary from tbl_registration)
order by salary desc
| Is This Answer Correct ? | 2 Yes | 0 No |
Answer / vishnu
Here we can find out 2nd highest salary in many ways,
according to the situation we can select anyone…
1st Type:
select min(esal) from emp where esal in (select top 2 esal
from emp order by esal desc)
2nd Type:
select max(esal) from emp where esal not in(select max(esal)
from emp)
3rd Type:
select max(esal) from emp where esal <(select max(esal) from
emp )
4th Type:
select Max(esal) from EMP a where 2=(select
COUNT(distinct(esal)) from EMP b where a.eSAL<=b.eSAL);
-----------------------------------------
For 2nd Lowest Salary:
-- 1st Type:
select min(esal) from emp where esal > (select min(esal)
from emp)
-- 2nd Type:
select * from emp where esal =
(
select max(esal) from emp where esal in (select top 2 esal
from emp order by esal)
)
| Is This Answer Correct ? | 2 Yes | 0 No |
Answer / sudhir
1. 2nd highest salary=>
Select top 1 * from (select top 2 * from emo order by sal
desc) order by sal asc
2. 2nd lowest salary
Select top 1 * from (select top 2 * from emo order by sal
Asc) order by sal Desc
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / sathish
select max(Salary)from employee where Salary not in (select
max(Salary)from employee )
select min(Salary)from employee where Salary not in (select
min(Salary)from employee )
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / mohsin mukri
select max(salary) as Salary from tbl_Employee
where salary < (select max(salary) from tbl_employee)
sory above post ans is wrong bcoz i forgot to remove = sign
| Is This Answer Correct ? | 1 Yes | 0 No |
Answer / mohsin mukri
select max(salary) as Salary from tbl_Employee
where salary <= (select max(salary) from tbl_employee)
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / pankaj kumar
if am not wrong we can also do the same thing by using aggreate function like Min or Max..
Like if i want to know 4th Highest Salary then i can
SELECT MIN(Sal) FROM TableName
WHERE Sal IN
(SELECT TOP 4 Sal FROM TableName ORDER BY Sal DESC)
Regards
Pankaj Kumar
pankaj.arrah@gmail.com
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / deepak
select max(amt)as amt from EmployeePaymentDetails where amt<
(select max(amt)as amt from EmployeePaymentDetails)
| Is This Answer Correct ? | 0 Yes | 0 No |
Answer / prasoon madnawat
i forgot order by in da last post
SQL Server:
Select MIN(Salery) from (SELECT TOP 2 * from EMPLOYEE order
by Salery DESC)
Oracle:
Select MIN(Salery) from (SELECT * from EMPLOYEE where ROWNUM
<3 order by Salery DESC)
MySQL
Select MIN(Salery) from (SELECT * from EMPLOYEE order by
Salery DESC LIMIT 2)
likewise for 2nd minimum salery.
| Is This Answer Correct ? | 1 Yes | 2 No |
Differences between VS 2005, 2008 and 2010 ?
what is web service?
Is .net easier than java?
What is code access security (cas)?
What is the mesi? : Dot net architecture
Can I create my own context attributes?
What other than biginteger has been introduced in system.numerics namespace?
What is the purpose of cache? : Dot net architecture
What is msil, and why should my developers need an appreciation of it if at all?
Explain the purpose of cache? How is it used? : .NET Architecture
How to improve the cache performance? : Dot net architecture
What is the .net class that allows the retrieval of a data element using a unique key?
Dot Net Framework 
