Query to find the third largest row in a table

[ranjitkumar]

Hi everybody,

create table employee
{
empno char(5) primay key,
name varchar2(30),
salary number(5,2)
};

1. For the above table how to find the employee with the third highest salary.

[willakawill]

Hi everybody,

create table employee
{
empno char(5) primay key,
name varchar2(30),
salary number(5,2)
};

1. For the above table how to find the employee with the third highest salary.

Hi. I would use a sub query and invert it;

SELECT TOP 1 empno, name, salary
FROM employee
WHERE empno IN
(SELECT TOP 3 empno
FROM employee
ORDER BY salary DESC)
ORDER BY salary

[ck1004]

Hi everybody,

create table employee
{
empno char(5) primay key,
name varchar2(30),
salary number(5,2)
};

1. For the above table how to find the employee with the third highest salary.

SELECT DISTINCT (A.salary) FROM EMPLOYEE A WHERE &N= (SELECT COUNT (DISTINCT (B.salary) FROM EMPLOYEE B WHERE A.salary<=B.sal ary);
Enter value for n :3

[willakawill]

SELECT DISTINCT (A.salary) FROM EMPLOYEE A WHERE &N= (SELECT COUNT (DISTINCT (B.salary) FROM EMPLOYEE B WHERE A.salary<=B.sal ary);
Enter value for n :3

where is the employee?

[suvam]

u can try with this one ------
Select * from employee where emp_no = (
Select emp_no from (Select * from employee order by sal desc) where rownum < n+1
Minus
Select emp_no from (Select * from employee order by sal desc) where rownum < n
) ;
Enter n = 3 ;

[pragatiswain]

u can try with this one ------
Select * from employee where emp_no = (
Select emp_no from (Select * from employee order by sal desc) where rownum < n+1
Minus
Select emp_no from (Select * from employee order by sal desc) where rownum < n
) ;
Enter n = 3 ;

Howz this:

Select * from (Select * from employee order by sal desc) where rownum < n+1
Minus
Select * from (Select * from employee order by sal desc) where rownum < n

[nunnasujatha]

Select * from employee where empid = (
Select empid from (Select * from employee order by sal desc) where rownum < &n+1
Minus
Select empid from (Select * from employee order by sal desc) where rownum < &n
) ;
enter n=3
enter n=3
or

Select * from employee where empid = (
Select empid from (Select * from employee order by sal desc) where rownum < &n
Minus
Select empid from (Select * from employee order by sal desc) where rownum < &n-1
) ;
enter n=4
enter n=4

but here we have to provide n value twice is there any other method???

[willakawill]

Wow. so much effort to answer one question when the first answer works perfectly. Still there is room for all :)

[nunnasujatha]

This is the problem with the code u have given

SQL> SELECT TOP 1 empno, name, salary
2 FROM employee
3 WHERE empno IN
4 (SELECT TOP 3 empno
5 FROM employee
6 ORDER BY salary DESC)ORDER BY salary;
SELECT TOP 1 empno, name, salary
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

[blazingrock4u]

SELECT b.sal FROM
(SELECT DISTINCT sal FROM emp) a,
(SELECT DISTINCT sal FROM emp) b
WHERE a.sal >= b.sal
GROUP BY b.sal
HAVING COUNT(b.sal) = 3;

[Pranav Joshi]

Hey Man,

Try This...

SELECT * FROM
(
SELECT ROWNUM, EMPNO, NAME, SALARY FROM employee
ORDER BY SALARY DESC
)
WHERE ROWNUM < 4
MINUS
SELECT * FROM
(
SELECT ROWNUM, EMPNO, NAME, SALARY FROM employee
ORDER BY SALARY DESC
)
WHERE ROWNUM < 3

[birdready2fly]

Hi everybody,

create table employee
{
empno char(5) primay key,
name varchar2(30),
salary number(5,2)
};

1. For the above table how to find the employee with the third highest salary.

hi ranjit
exequte this query u will get perfect ans
select sal from emp e where &n-1 = (select count(distinct( sal)) from emp d where e.sal < d.sal ) order by sal asc

[gvprasad81]

select highest_sal from jobgrads where highest_sal=(se lect max(highest_sal ) from jobgrads
where highest_sal <(select max(highest_sal ) from jobgrads where highest_sal <
(select max(highest_sal ) from jobgrads)))

[gvprasad81]

select max(highest_sal ) from jobgrads where highest_sal < (select max(highest_sal ) from jobgrads where highest_sal <(select max(highest_sal ) from jobgrads))

[krishhhna]

Hi everybody,

create table employee
{
empno char(5) primay key,
name varchar2(30),
salary number(5,2)
};

1. For the above table how to find the employee with the third highest salary.

here is the query to select third highest salary.

select level,max(sal) from employee where level=3 connect by prior sal > sal group by level;