how to get second highest sal of emp table
Answers were Sorted based on User's Feedback
Answer / yadunandan
select max(sal) from emp where sal<(select max(sal) from
emp)
Is This Answer Correct ? | 4 Yes | 2 No |
Answer / anilchandu
Select * from emp x where 2=(select count(distinct sal)
from emp y where x.sal<=y.sal)
where x,y are alias names.
If you want 3rd heighest salary then just replace 2 by 3
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / manjeet
select max(sal) from emp where sal not in (select max(sal)
from emp)
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / prabhudatta barick
--IN ORDER TO FIND THE NTH HIGHEST SALARY,
--WE JUST FIND THE SALARY THAT HAS EXACTLY N-1 SALARIES
GREATER THAN ITSELF---
--This is correlated subquery--
select empno,
ename,
sal
from scott.emp e
where &n-1=(select count(distinct b.sal) from scott.emp b
where b.sal>e.sal);
To get second highest salary put n value 2.
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / rakhi
SELECT *
FROM (
SELECT employee_id, last_name, salary,
RANK() OVER (ORDER BY salary DESC) EMPRANK
FROM employees)
WHERE emprank = 2;
Is This Answer Correct ? | 2 Yes | 2 No |
Answer / amit bhagat
select min(a.sal) from (select disctinct d.sal from emp d
order by sal desc) a where rownum<=2
Is This Answer Correct ? | 0 Yes | 0 No |
What is an oracle recycle bin?
How to run create database statement again?
How to install oracle odbc drivers?
What is bulk collect in oracle?
What is the difference between SQL and SQL Server?
How to declare a local variable?
Can you use a commit statement within a database trigger?
How to call a stored function in oracle?
I have two tables : COuntry,city I want all the cities in each country.Question on Cross Join.
How to create a single index for multiple columns?
What is Trace File ?
What is blob data type in oracle?