Find out nth highest salary from emp table

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal < = b.sal);
For Eg:-
Enter value for n: 2
SAL
---------
3700

Showing Answers 1 - 75 of 193 Answers

prathima

  • Sep 19th, 2005
 

SELECT mod(ROWNUM,2),sup_cd, qtn_price FROM (SELECT sup_cd, qtn_price FROM TB_B_sup_price C1 ORDER BY qtn_price DESC) WHERE ROWNUM<=n+1 MINUSSELECT mod(ROWNUM,2),sup_cd, qtn_price FROM (SELECT sup_cd, qtn_price FROM TB_B_sup_price C1 ORDER BY qtn_price DESC) WHERE ROWNUM<=n-1 order by qtn_price desc

  Was this answer useful?  Yes

sayeed

  • Jan 2nd, 2006
 

directly we can not give the condition as rownum=2....this is the most optimal soluton for the given problemselect * from (select rownum r,salary from (select distinct(salary)from employees where salary is NOT NULL order by salary desc)) where r=2try out this.

  Was this answer useful?  Yes

Bharath B S

  • Jan 18th, 2006
 

we cant use rownum=n so u can use this select min(sal) "nth highest sal" from emp where sal in (select sal from (select DISTINCT(sal) from emp order by sal desc) where rownum<=n)

  Was this answer useful?  Yes

Bharath B S

  • Jan 18th, 2006
 

we cant use rownum=n so u can use this select min(sal) "nth highest sal" from emp where sal in (select sal from (select DISTINCT(sal) from emp order by sal desc) where rownum<=n)

  Was this answer useful?  Yes

vijayakumar

  • Jun 4th, 2006
 

select * from
(
select rownum rn,sal from
(select distinct(sal) from emp order by sal)) x
where x.rn=5;

  Was this answer useful?  Yes

sreeprem

  • Jun 6th, 2006
 

thiswill work if u give the number instead of n for ex:

gives the Second highest Salary n the table

select min(salary) from xxyy where value in(select top 2 alary from xxyy order by value desc)

  Was this answer useful?  Yes

Sushil Kumar Vishwakarma

  • Nov 7th, 2006
 

Best way is using CORRELATED SUBQUERY : as below

Select * From TableName T1 Where
    (N-1) = (Select Count(Distinct(E2.ColumnName)) From TableName  T2 Where   T2.ColumnName > T1.ColumnName)

For Required Case We can use it as :-

Select * From EMP T1 Where
    (2-1) = (Select Count(Distinct(E2.sal)) From EMP  T2 Where   T2.sal > T1.sal)

  Was this answer useful?  Yes

the_xxx

  • Mar 27th, 2007
 

The best way is to do using Rank.

SELECT * FROM (select COL1,COL2, dense_rank() OVER (order BY COLUMN_TO_SELECT_NTH_VAL) rank from TABLE_NAME) WHERE rank=N;

NITIN R

  • Mar 30th, 2007
 

Try this:

SELECT RANK,SALARY FROM(SELECT ROWNUM AS RANK,SALARY FROM(SELECT DISTINCT SALARY FROM EMPLOYEES WHERE SALARY IS NOT NULL ORDER BY SALARY DESC)) WHERE RANK =n;

sunil

  • Mar 30th, 2007
 

can u try this it is much better than this i think
select * from emp(select rownum rn,sal from (select * from emp order by sal desc))
where rn=&m



u can dynamically enter which highest sal u can get it

tina_0091

  • Apr 3rd, 2007
 

hi,

Use the top-analysis feature which introduce from oracle 8i:

SELECT rownum, empno, ename, sal 
FROM (SELECT empno, ename, sal from emp
           ORDER BY sal desc )
WHERE rownum < n; (n=any valid integer)

Thanks,
Tina K.

Animesh

  • May 25th, 2007
 

Hi
I think the best way of doing this is given here........

1  select b.sal from
2  (select distinct sal from emp) a,
3  (select distinct sal from emp) b
4  where a.sal>=b.sal
5* group by(b.sal) having count(b.sal)=n;

try it ......to replace n with the number u want

  Was this answer useful?  Yes

nidhi

  • Jul 11th, 2007
 

Hi,

try this...

select max(sal)
from emp
where rownum<=&v
order by sal desc;

  Was this answer useful?  Yes

soumyapradh

  • Jul 21st, 2007
 

This one is the most appropiate answer
SELECT LEVEL, MAX(Sal) FROM Emp WHERE LEVEL = &LevelNo CONNECT BY PRIOR Sal > Sal GROUP BY LEVEL

  Was this answer useful?  Yes

varma indukuri

  • Jul 30th, 2007
 

This also works:

select
* from

 (select * from

  (select * from

   (select distinct(salary)

    from employee

    order by 1 desc
    )

   where rownum < (&N+1)
  )

  order by 1 asc
 )

where rownum=1

  Was this answer useful?  Yes

yadav.hitendra

  • Aug 13th, 2007
 

(1)

select * from empl a
where 9=(select count(*) from (select distinct salary from empl)b

where b.salary>=a.salary);

----------------------------------
In this query in mqin query use 9= for finding 9th highest salary employee,
                                          9>= for finding nine high salary employees,
                                          9<= for finding nine low salary employees.
----------------------------------
or we can use this query also-
----------------------------------
(I) for finding 9th highest salary:-

select max(salary) from (select * from (select distinct salary from empl order by salary desc)where

rownum<=9);

(II) for finding nine high salary employees :-

select * from (select distinct salary from empl order by salary desc)where

rownum<=9;


(III) for finding nine low salary employees :-

select * from (select distinct salary from empl order by salary )

where rownum<=9;
 

  Was this answer useful?  Yes

vivek singh

  • Aug 15th, 2007
 

To find second highest salary we cannot use = operator in TOP N analysis

select rownum,sal from (select sal from emp where sal <(select max(sal) from emp) order by sal desc) where rownum < 2

  Was this answer useful?  Yes

appidixyz

  • Sep 20th, 2007
 

n=9th

select a.empno,rank() over(order by sal) sal_rank from emp a
 select * from(
 select a.empno,rank() over(order by sal) sal_rank from emp a
 )where sal_rank=9;

  Was this answer useful?  Yes

heloevry1

  • Sep 21st, 2007
 

To get nth highest sal:
This is the easy way to get that:

for eg:
5th
 
select max(sal) from emp where sal  not in (select  top 4 sal from emp order by sal desc)


OR

select min(sal) from emp where sal in (select  top 5 sal from emp order by sal desc)

  Was this answer useful?  Yes

Vikram

  • Oct 3rd, 2007
 

Shortest Query

SELECT MIN(e.salary) FROM (SELECT salary
FROM employee ORDER BY salary DESC) e WHERE rowid <= n;

  Was this answer useful?  Yes

susi

  • Oct 25th, 2007
 

select sal from (select distinct sal from emp order by desc)
where rownum < &&n
minus
select sal from (select distinct sal from emp order by desc)
where rownum < (&n -1)

  Was this answer useful?  Yes

Veena B

  • Feb 5th, 2010
 

SELECT MIN(sal)
FROM emp
WHERE sal IN (SELECT sal
                    FROM (SELECT DISTINCT sal
                             FROM emp
                             ORDER BY sal DESC)
                    WHERE rownum <= n);
 

  Was this answer useful?  Yes

kishordey

  • Feb 17th, 2010
 

select * from (SELECT emp_id, ROW_NUMBER() OVER(ORDER BY  salary DESC) AS salary, PostalCode 

FROM salary
)a
where a.salary=n

a=1,2,3,4,................

Put a's value whatever u want............


  Was this answer useful?  Yes

vidya.R

  • Mar 2nd, 2010
 

SELECT MIN(salary) FROM (SELECT DISTINCT TOP 6 salary FROM empinfo ORDER BY salary desc) empinfo;

This query works perfectly in MS SQL SERVER

  Was this answer useful?  Yes

Empsal table data
700
500
100
900
400
200
600
750


Query to find second highest salary from table Empsal


mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary<(select max(salary) from Empsal));


Output=750


Query to find third highest salary from table Empsal


mysql>select distinct(max(e.salary)) from Empsal e
>where e.salary in(
>select e1.salary from Empsal e1 where e1.salary<
>(select max(e1.salary) from Empsal e1
>where e1.salary IN(
>select e2.salary from Empsal e2 where
>e2.salary<(select max(salary) from Empsal))));


Output=700

  Was this answer useful?  Yes

select column1,column2 from tablename(ex:sailors s1) where n-1=(
select count(*) from sailors s2  where s2.rating>s1.rating);

Explanation : The sub query selects the count number in descending order just like an array.
                  Then array like indexes matches to n-1 to give exact result;
                  Request:please correct it if any mistakes. i have executed it and got good results.

  Was this answer useful?  Yes

ranjith.avi

  • Jan 10th, 2011
 

Query:

select min(salary) from (select distinct salary from ranjith1 where salary is NOT NULL order by salary desc) where rownum<=&n;

Here the n is nth salary.
It will work perfectely..

Thanks
Ranjith

  Was this answer useful?  Yes

Sikindar

  • Mar 11th, 2011
 

select * from emp where sal=(
SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal<=b.sal));

  Was this answer useful?  Yes

select * from emp
where sal in(select max(sal) from emp
                    where level=&levelno
                    connect by prior sal>sal
                     group by level)




Thanks&Regards
Kethinenisarath

  Was this answer useful?  Yes

asis sarma

  • Jul 18th, 2011
 

How does the following query work to find the nth highest salary

Code
  1.  SELECT DISTINCT(A.SALARY) FROM EMPLOYEE A

  2. WHERE &SALARY = (SELECT COUNT(DISTINCT(B.SALARY)) FROM EMPLOYEE B

  3. WHERE A.SALARY<=B.SALARY);


thanks
Asis

  Was this answer useful?  Yes

bhaskar sit mca

  • Jul 25th, 2011
 

You can find Nth highest salary in emp table using the following query.You can replace the Nth value with what u want.....

select min(sal) max from (select * from emp order by sal desc) where rownum<=N;

  Was this answer useful?  Yes

bhaskar sit

  • Jul 25th, 2011
 

To find Nth highest sal in emp table......use the following query .You can replace it N with what ever number u want......
select min(sal) from (select * from emp order by sal desc) where rownum<=N;

for example for 5th highest salary:
select min(sal) max from (select * from emp order by sal desc) where rownum<=5;

  Was this answer useful?  Yes

bhaskar

  • Jul 25th, 2011
 

To find Nth highest sal in emp table......use the following query .You can replace it N with what ever number u want......
select min(sal) from (select * from emp order by sal desc) where rownum<=N;

for example for 5th highest salary:
select min(sal) max from (select * from emp order by sal desc) where rownum<=5;

  Was this answer useful?  Yes

Aarti Vyas

  • Jul 25th, 2011
 

Code
  1. SELECT Top n DISTINCT Emp_Sal

  2. FROM Emp

  3. ORDER BY Emp_Sal DESC

  4.  

  Was this answer useful?  Yes

Prabakaran

  • Jul 26th, 2011
 

select sal from (select sal from emp where sal is not null order by sal desc) where rownum=n

  Was this answer useful?  Yes

Rohit kumar

  • Jul 31st, 2011
 

Select * from < table name > e where
row num=(select count (distinct coloumn name) from < table name > where e.coloumn name < = coloumn name)

For example:
we have to find the six th maximum salary from the table employee

Ans:

select * from employee e where
6=(select count(distinct salary ) from employee where e.salary < = salary)

Code
  1. SELECT * FROM <table name> e WHERE

  2. ROW num=(SELECT COUNT (DISTINCT coloumn name) FROM <table name>  WHERE e.coloumn name<=coloumn name)

  3.  

  4. FOR example:

  5. we have TO find the six th maximum salary FROM the TABLE employee

  6.  

  7. Ans:

  8.  

  9. SELECT * FROM employee e WHERE

  10. 6=(SELECT COUNT(DISTINCT salary ) FROM employee WHERE e.salary<=salary)

  Was this answer useful?  Yes

Chandan

  • Aug 9th, 2011
 

SELECT LEVEL,MAX(sal) --SELECTING level and highest salary
FROM EMP
CONNECT BY PRIOR sal > sal --checking nth highest salary by optimiser
WHERE LEVEL=&LEVEL -- allow user to enter a number to see thatmuch highest salary
GROUP BY LEVEL;

  Was this answer useful?  Yes

srimanta sahoo

  • Aug 17th, 2011
 

Code
  1.  SELECT Level , Max(sal) FROM <Table_Name>

  2.           WHERE Level=&Level

  3.          Connect BY Prior sal>sal

  4.          GROUP BY Level

  Was this answer useful?  Yes

Sethuraman

  • Aug 19th, 2011
 

Hi Guys,

Code
  1. SELECT MIN(sal)                                        

  2. FROM                                                            

  3. (                                                              

  4. SELECT                                                          

  5.        DISTINCT(sal)                                  

  6.         FROM                                                    

  7.       prsn t1                                          

  8.   ORDER BY sal  DESC                          

  9.   FETCH FIRST N ROWS ONLY                                      

  10. )  AS A  

  11.  

  12.  


This query is efficient way to compare all other queries.
Because all other queries using Joins. But I have used sub query concept.

  Was this answer useful?  Yes

gopal sharma

  • Aug 27th, 2011
 

select min(sal)
from table_name
where sal in (select top N sal
from table_name
order by sal desc);

  Was this answer useful?  Yes

For this 2 ways are there.
1.select sal from (select sal from emp order by sal desc) where rownum <= n
minus
select sal from (select sal from emp order by sal desc) where rownum < n

2.select sal from emp e1 where (n-1)=select count(*) from emp e2 where e1.salary > e2.salary

But first approach is best approach because in second case one row of first table is compared with all the rows of second table.So performance will be reduced.

  Was this answer useful?  Yes

Code
  1. SELECT ROWNUM,empno,ename,sal,job FROM (SELECT empno,ename,sal,job FROM emp ORDER BY sal DESC) GROUP BY empno,ename,sal,job WHERE ROWNUM=&n

  2.  

  Was this answer useful?  Yes

vamsi krishna devineni

  • Nov 27th, 2011
 

select * from emp e1 where &n=(select count(*) from emp e2 where e1.sal < e2.sal);

in this query n=0 means first highest
n=1 means second highest
n=2 means third highest

  Was this answer useful?  Yes

kranthi swaroop

  • Dec 2nd, 2011
 

Code
  1. SELECT  DISTINCT MAX(SAL),ENAME

  2. FROM EMP

  3. WHERE SAL = (SELECT MAX(SAL) FROM EMP)

  4. --WHERE ROWNUM =1

  5. GROUP BY ENAME

  6. ORDER BY 1 ASC


Hope this will workout as per your requirement

  Was this answer useful?  Yes

sameer

  • Jan 10th, 2012
 

Code
  1. SELECT min(sal) FROM (SELECT DISTINCT sal FROM emp ORDER BY sal DESC) WHERE rownum<=N

  2.  

  Was this answer useful?  Yes

Abhishek

  • Jan 15th, 2012
 

Code
  1. SELECT ename,empno,sal FROM (

  2. SELECT ename,empno,sal FROM scott.emp ORDER BY sal DESC)

  3. GROUP BY rownum,ename,empno,sal

  4. HAVING rownum = 5

  Was this answer useful?  Yes

Prabhu Shankar

  • Jan 17th, 2012
 

Hi..

your query returns fisrt 5 max salaries but the question is nth maximum salary so your query will be :

select min(salary) Salary from (select salary from employees where salary is NOT NULL order by salary desc) where rownum < = 5

here it will show 5th max salary.

Try this and let me know if my query is wrong.

  Was this answer useful?  Yes

rupesh

  • May 4th, 2012
 

Code
  1. SELECT rownum,salary,last_name

  2. FROM (SELECT salary,last_name FROM employees ORDER BY salary DESC)

  3. WHERE rownum<=n;


where n is the number of person you want to show.
eg: if n=3,itll show first three values.

  Was this answer useful?  Yes

nain aggarwal

  • May 29th, 2012
 

Easiest way to find the nth highest salary

Code
  1.  

  2. SELECT salary FROM employees WHERE salary=

  3. (SELECT MAX(salary) FROM (SELECT DISTINCT salary FROM employees WHERE rownum<=n-1 ORDER BY salary DESC));

  4. n=nth employee salary

  5.  

  Was this answer useful?  Yes

Rahman,TCS

  • May 31st, 2012
 

May be we can have the right results with the above queries as mentioned above.... pls try to use the one below to understand clearly to get the 3rd max sal.

Select Esal from (Select Esal from Emp order by Desc) where Rownum<4
Minus
Select Esal from (Select Esal from Emp order by Desc) where Rownum<3

  Was this answer useful?  Yes

rohitosu

  • Jul 30th, 2012
 

Code
  1.  SELECT * FROM  (SELECT employee_id, salary, dense_rank() OVER ( ORDER BY salary DESC)  r

  2.   FROM employees) a

  3.   WHERE a.r =3 ;

  Was this answer useful?  Yes

bhavikgore

  • Oct 27th, 2012
 

This will work for only highest salary n=1 but not work for 2nd and rest nth salary...
Did you have tried this?

  Was this answer useful?  Yes

Sivaji Bonu

  • Nov 28th, 2014
 

Code
  1. WITH records

  2. AS

  3. (

  4.     SELECT e.*,

  5.             DENSE_RANK() OVER ( ORDER BY sal DESC) rn

  6.     FROM    emp e

  7. )

  8. SELECT  *

  9. FROM    records

  10. WHERE   rn = &n;


Note :: Here n is No of highest sal position ;i.e n=1,2,3.....

  Was this answer useful?  Yes

Sivaji Bonu

  • Nov 28th, 2014
 

Hi bhavikgore,

I got the output and The given query work for Nth Height saL.......

Code
  1.  

  2. WITH records

  3. AS

  4. (

  5.     SELECT e.*,

  6.             DENSE_RANK() OVER ( ORDER BY sal DESC) rn

  7.     FROM    emp e

  8. )

  9. SELECT  *

  10. FROM    records

  11. WHERE   rn <=2;

  Was this answer useful?  Yes

mohammed khan

  • Dec 5th, 2014
 

Code
  1. SELECT * FROM (SELECT S.*,DENSE_RANK() OVER (ORDER BY SALARY DESC) DR FROM SOURCE S) S WHERE S.DR=9;

  Was this answer useful?  Yes

Dasish

  • Dec 12th, 2014
 

Select a.empno, a.sal from emp a, emp b where a.sal <= b.sal group by a.empno, a.sal having count( distinct b.sal ) = &n

  Was this answer useful?  Yes

AKSHATHA

  • Dec 25th, 2014
 

Use rank funtion -
SELECT * FROM (
SELECT empno,rank() OVER (ORDER BY sal desc) rn FROM emp)
WHERE rn = 1

  Was this answer useful?  Yes

Bhalchandra L.

  • Apr 15th, 2015
 

Could you please provide me the step by step detailed execution logic of this query as this was asked in one of my interview .

  Was this answer useful?  Yes

Ejaz Ahmed

  • Apr 19th, 2015
 

Here is a simple answer for finding nth salary or number, just change any number that you want to find after Where.

SELECT a.salary from employees a
where 4 = (SELECT DISTINCT COUNT(b.salary) from employees b where a.salary <= b.salary )

  Was this answer useful?  Yes

Here is a step by step execution logic,
Consider the below employee table structure

Code
  1. EMP_ID   SALARY

  2. 101         25000

  3. 102         37000

  4. 104         30000

  5. 105         43000

  6.  

  7. Query TO find 1st Maximum salary

  8. SELECT salary

  9. FROM employee e1

  10. WHERE 0 = (SELECT COUNT(*)

  11.            FROM employee e2

  12.            WHERE e1.salary <e2.Salary);

  13. OUTPUT : 43000

  14.  

  15.       Iteration 1                         Iteration 2                        Iteration 3                      Iteration 4                  

  16. 25000 < 25000 ->0          37000 < 25000 ->0           30000 < 25000 ->0          43000 < 25000 ->0

  17. 25000 < 37000 ->1          37000 < 37000 ->0           30000 < 37000 ->1          43000 < 37000 ->0

  18. 25000 < 30000 ->1          37000 < 30000 ->0           30000 < 30000 ->0          43000 < 30000 ->0

  19. 25000 < 43000 ->1          37000 < 43000 ->1           30000 < 43000 ->1          43000 < 43000 ->0

  20. count(*) - 3                  count(*) - 1                   count(*) - 2                  count(*)-0

  21. 25000 - 4th Max Salary    37000 – 2nd Max Salary   30000 – 3rd Max Salary   43000 – 1st Max Salary


  Was this answer useful?  Yes

Bose

  • Jul 17th, 2015
 

Code
  1. SELECT sal

  2. FROM(SELECT sal,DENSE_RANK() over(ORDER BY sal DESC) AS bb FROM emp)WHERE bb=3;


bb=nth value

  Was this answer useful?  Yes

SREENIVASULU

  • Sep 10th, 2015
 

SELECT * FROM EMP E1 WHERE &N_1= (SELECT COUNT(DISTINCT(E2.SAL)) FROM EMP E2 WHERE E1.SAL

  Was this answer useful?  Yes

VISHWA

  • Mar 10th, 2018
 

Here for ROWNUM =1 it will work but for ROWNUM = 2 and so on it will not work.

  Was this answer useful?  Yes

Silpa

  • Apr 18th, 2018
 

Select employee_id,salary, rownumber from (Select employee_id,salary,row_number() over (order by salary desc) as rownumber from hr.employees)
where rownumber = n;

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions