Interested to Buy Any Domain ? << Click Here >> for more details...
how to get second highest sal of emp table
- 6 Answers
- 9918 Views
- I also Faced
- E-Mail Answers
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 ? | 5 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 key preserved table?
What happens to the current transaction if the session is killed?
How can you tell how much space is left on a given file system and how much space each of the file systems subdirectories take-up?
What is the Extension of Oracle Database? As extension of Access Database is .mdb
What is Hash Cluster ?
Explain the blob datatype?
How to use subqueries with the exists operator in oracle?
5. Display full details for the creditor/s who has received the single largest payment. Do not use a table join or set operator anywhere in your query.
What are the differences between a sys and system user and what are the extra privileges available to the sys user?
What are the attributes of cursor?
State all possible different index configurations a table can possibly have?
How to rename an index?
Oracle General 
