find out the third highest salary?
Answers were Sorted based on User's Feedback
Answer / dilip tiwari
THis is for Oracle
SELECT * FROM emp
(SELECT empname,Salary,dense_rank() OVER(ORDER BY salary
DESC) AS ranking FROM emp)
WHERE ranking = 3
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / bhagayraj
SELECT *
FROM Emp e1
WHERE (2) = (
SELECT COUNT(DISTINCT(e2.Salary))
FROM Emp e2
WHERE e2.Salary > e1.Salary)
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / rajeevgeu
select rownum,salary from(select rownum r,salary from emp order by salary)where r=3;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / madhu
To find nth salary:
SELECT SUBSCRIBER_no
FROM (SELECT SUBSCRIBER_NO, ROWNUM R
FROM (SELECT DISTINCT SUBSCRIBER_NO
FROM SUBSCRIBER
ORDER BY SUBSCRIBER_NO DESC))
WHERE R = &R
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / sukanya
select max(sal) from emp where sal<
(select max(sal) from emp where sal <
(select max(sal) from emp));
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / daji surwase
select salary
from employees
order by salary desc
offset 2 rows fetch next 1 row only;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / chandiran
select * from (select sal, rownum r from Employee
order by sal desc)
where r=3
Is This Answer Correct ? | 1 Yes | 2 No |
Answer / ayaz mahmood
select max(sal) from emp
where sal= (select max(sal) from emp
where sal< (select max(sal) from emp
where sal< (select max(sal) from emp)))
Is This Answer Correct ? | 1 Yes | 2 No |
Answer / haneef
Best One
select top 1 * from test_emp where sal in (select top 3 sal
from test_emp order by sal desc) order by sal asc
or
It also work
select top 1 * from test_emp where sal in (select top 3 sal
from test_emp order by sal desc) order by sal asc
select top 1 MAX(sal),id from test_emp where sal < (select
max(sal) from test_emp where sal < (select max(sal) from
test_emp))
group by id,sal order by sal desc
Is This Answer Correct ? | 0 Yes | 1 No |
Answer / abhay
Select Top 1 sal from (select distinct top 3 sal from emp
order by sal desc)a order by sal
Is This Answer Correct ? | 0 Yes | 1 No |
What is a Garbage Collection? and what is full recursive Garbage collection?
How to call a sub procedure?
How to define an oracle sub procedure?
How to create an oracle database manually?
What is a nested table?
How many types of auditing in Oracle?
Why do you create or replace procedures rather that drop and recreate.
How do you recover a datafile that has not been physically been backed up since its creation and has been deleted. Provide syntax example.
how to truncate date and get only time part 9:20:00
pls explain connect by prior with example and its real time use
how to retrieve data from different tables ,place this data in different excel worksheets.
How to use values from other tables in update statements using oracle?