GeekInterview.com
Series: Subject: Topic:
Question: 407 of 429

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
Asked by: Interview Candidate | Asked on: Aug 29th, 2004
Showing Answers 1 - 62 of 62 Answers
prathima

Answered On : 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

  
Login to rate this answer.
maverickwild

Answered On : Nov 15th, 2005

View all answers by maverickwild

Try this oneselect sal from (select distinct(sal) from emp where sal is NOT NULL order by sal dsc) where rownum =n

Yes  1 User has rated as useful.
  
Login to rate this answer.
sayeed

Answered On : 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.

  
Login to rate this answer.
Bharath B S

Answered On : 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

  
Login to rate this answer.
Bharath B S

Answered On : 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

  
Login to rate this answer.
nileshsingh

Answered On : May 18th, 2006

View all answers by nileshsingh

SELECT LEVEL,MAX(SAL) FROM WHERE LEVEL = NCONNECT BY PRIOR SAL>SALGROUP BY LEVEL;

  
Login to rate this answer.
vijayakumar

Answered On : Jun 4th, 2006

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

  
Login to rate this answer.
sreeprem

Answered On : Jun 6th, 2006

View all answers by sreeprem

thiswill work if u give the number instead of n for ex:gives the Second highest Salary n the tableselect min(salary) from xxyy where value in(select top 2 alary from xxyy order by value desc)

  
Login to rate this answer.
gannesunil

Answered On : Aug 2nd, 2006

View all answers by gannesunil

select * from (select rownum rn,sal from(select * from emp order by sal desc))where rn=&m;

  
Login to rate this answer.
Sushil Kumar Vishwakarma

Answered On : Nov 7th, 2006

Best way is using CORRELATED SUBQUERY : as belowSelect * 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)

  
Login to rate this answer.
the_xxx

Answered On : Mar 27th, 2007

View all answers by the_xxx

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;

Yes  2 Users have rated as useful.
  
Login to rate this answer.
NITIN R

Answered On : 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;

  
Login to rate this answer.
sunil

Answered On : Mar 30th, 2007

can u try this it is much better than this i thinkselect * from emp(select rownum rn,sal from (select * from emp order by sal desc))where rn=&mu can dynamically enter which highest sal u can get it

Yes  2 Users have rated as useful.
  
Login to rate this answer.
tina_0091

Answered On : Apr 3rd, 2007

View all answers by tina_0091

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.

Yes  1 User has rated as useful.
  
Login to rate this answer.
Animesh

Answered On : May 25th, 2007

HiI think the best way of doing this is given here........1  select b.sal from2  (select distinct sal from emp) a,3  (select distinct sal from emp) b4  where a.sal>=b.sal5* group by(b.sal) having count(b.sal)=n;try it ......to replace n with the number u want

  
Login to rate this answer.
nidhi

Answered On : Jul 11th, 2007

Hi,try this...select max(sal)from empwhere rownum<=&vorder by sal desc;

  
Login to rate this answer.
soumyapradh

Answered On : Jul 21st, 2007

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

  
Login to rate this answer.
varma indukuri

Answered On : 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

  
Login to rate this answer.
yadav.hitendra

Answered On : Aug 13th, 2007

(1)select * from empl a where 9=(select count(*) from (select distinct salary from empl)bwhere 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; 

  
Login to rate this answer.
vivek singh

Answered On : 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

  
Login to rate this answer.
appidixyz

Answered On : Sep 20th, 2007

View all answers by appidixyz

N=9thselect 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;

  
Login to rate this answer.
heloevry1

Answered On : 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)ORselect min(sal) from emp where sal in (select  top 5 sal from emp order by sal desc)

  
Login to rate this answer.
Vikram

Answered On : Oct 3rd, 2007

Shortest QuerySELECT MIN(e.salary) FROM (SELECT salary
FROM employee ORDER BY salary DESC) e WHERE rowid

  
Login to rate this answer.
susi

Answered On : Oct 25th, 2007

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

  
Login to rate this answer.

SELECT v.sal FROM (SELECT TO_NUMBER(salary) sal,rank() over (ORDER BY TO_NUMBER(salary) DESC) ranksal FROM employee ORDER BY 1 DESC)v WHERE v.ranksal=6 Pls note where 6 is the ranknumber

  
Login to rate this answer.
Veena B

Answered On : Feb 5th, 2010

View all answers by Veena B

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

  
Login to rate this answer.
kishordey

Answered On : Feb 17th, 2010

View all answers by kishordey

select * from (SELECT emp_id, ROW_NUMBER() OVER(ORDER BY salary DESC) AS salary, PostalCode FROM salary)a where a.salary=na=1,2,3,4,................Put a's value whatever u want............

  
Login to rate this answer.
vidya.R

Answered On : Mar 2nd, 2010

View all answers by vidya.R

SELECT MIN(salary) FROM (SELECT DISTINCT TOP 6 salary FROM empinfo ORDER BY salary desc) empinfo;This query works perfectly in MS SQL SERVER

  
Login to rate this answer.
vidya.R

Answered On : Mar 2nd, 2010

View all answers by vidya.R

SELECT min(salary) FROM (SELECT DISTINCT TOP 6 salary FROM empinfo ORDER BY salary DESC) empinfo;

  
Login to rate this answer.
pradyumna k j

Answered On : Jul 31st, 2010

View all answers by pradyumna k j

SELECT salaryFROM employeeORDER BY salary DESCLIMIT n-1,1

  
Login to rate this answer.
amitsing2008

Answered On : Aug 10th, 2010

View all answers by amitsing2008

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

  
Login to rate this answer.
bodduramesh

Answered On : Oct 8th, 2010

View all answers by bodduramesh

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.

  
Login to rate this answer.
ranjith.avi

Answered On : Jan 10th, 2011

View all answers by ranjith.avi

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..ThanksRanjith

  
Login to rate this answer.
Sikindar

Answered On : Mar 11th, 2011

View all answers by Sikindar

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));

  
Login to rate this answer.

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

  
Login to rate this answer.
sankar babu

Answered On : May 25th, 2011

View all answers by sankar babu

Select level,max(sal)from empgroup by level;

  
Login to rate this answer.
asis sarma

Answered On : 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

  
Login to rate this answer.
bhaskar sit mca

Answered On : 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

  
Login to rate this answer.
bhaskar sit

Answered On : 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

  
Login to rate this answer.
bhaskar

Answered On : 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

  
Login to rate this answer.
Aarti Vyas

Answered On : Jul 25th, 2011

Code
  1. SELECT Top n DISTINCT Emp_Sal
  2. FROM Emp
  3. ORDER BY Emp_Sal DESC
  4.  

  
Login to rate this answer.
Prabakaran

Answered On : Jul 26th, 2011

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

  
Login to rate this answer.
Rohit kumar

Answered On : 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)

  
Login to rate this answer.
Chandan

Answered On : 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;

  
Login to rate this answer.
srimanta sahoo

Answered On : 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

  
Login to rate this answer.
Sethuraman

Answered On : 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.

  
Login to rate this answer.
gopal sharma

Answered On : Aug 27th, 2011

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

  
Login to rate this answer.

For this 2 ways are there.
1.select sal from (select sal from emp order by sal desc) where rownum 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.

  
Login to rate this answer.
dinesh.smhdr

Answered On : Sep 13th, 2011

View all answers by dinesh.smhdr

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.  

  
Login to rate this answer.
vamsi krishna devineni

Answered On : 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

  
Login to rate this answer.
kranthi swaroop

Answered On : 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

  
Login to rate this answer.
sameer

Answered On : Jan 10th, 2012

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

  
Login to rate this answer.
Abhishek

Answered On : 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

  
Login to rate this answer.
Prabhu Shankar

Answered On : 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.

  
Login to rate this answer.
Komalrad

Answered On : Feb 16th, 2012

View all answers by Komalrad

Code
  1. SELECT min(sal) FROM (SELECT DISTINCT(sal) FROM emp ORDER BY DESC LIMIT n)

  
Login to rate this answer.
bharathtele

Answered On : Apr 26th, 2012

View all answers by bharathtele

Select Emp_Name from emp_table where Id=(Select Emp_Id from Salary Order By salary Desc limit 0,1)

  
Login to rate this answer.
saketp

Answered On : Apr 29th, 2012

View all answers by saketp

This is the simplest of all :)

Code
  1. SELECT * FROM(SELECT ROWNUM AS a,salary FROM employees ORDER BY salary DESC) WHERE a = "enter your nth value here";

  
Login to rate this answer.
rupesh

Answered On : 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.

  
Login to rate this answer.
nain aggarwal

Answered On : 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.  

  
Login to rate this answer.
Rahman,TCS

Answered On : 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

  
Login to rate this answer.
rohitosu

Answered On : Jul 30th, 2012

View all answers by rohitosu

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 ;

  
Login to rate this answer.
bhavikgore

Answered On : 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?

  
Login to rate this answer.

Give your answer:

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

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.