GeekInterview.com
Series: Subject: Topic:
Question: 348 of 427

sql query to get 4th or 5th maximum value from a table

Asked by: Interview Candidate | Asked on: Nov 2nd, 2005
Showing Answers 1 - 17 of 17 Answers
Jayakumar M

Answered On : Nov 8th, 2005

Select * from (select rownum rn,sal  from (select distinct sal From emp order by sal desc)) where rn between 4 and 5;

  
Login to rate this answer.
Anonymous

Answered On : Dec 25th, 2005

If the DB does not support ranking/rownumbering (like SQL 2000), then that's a bit different. This is just a simple way to do this, probably not the most optimal (please test):select top5.value from(select top 5 value from table t order by t.value asc) as top5left outer join(select top 3 value from table t order by t.value asc) as top3on top5.value = top3.valuewhere top3.value is null

  
Login to rate this answer.
Elango

Answered On : Jan 19th, 2006

4th MAXselect max(id) from EMP A where 4=( select count(id) From EMP  B where B.ID>=A.ID)5th MAXselect max(id) from EMP A where 5=( select count(id) From EMP  B where B.ID>=A.ID)

  
Login to rate this answer.
pktelango

Answered On : Jan 19th, 2006

View all answers by pktelango

4th MAxselect max(id) from EMP A where 4=( select count(id) From EMP  B where B.ID>=A.ID)5th MAxselect max(id) from EMP A where 5=( select count(id) From EMP  B where B.ID>=A.ID)Nth MAxselect max(id) from EMP A where N=( select count(id) From EMP  B where B.ID>=A.ID)

  
Login to rate this answer.
Rahman

Answered On : Jul 17th, 2006

Pls Explain the query

  
Login to rate this answer.
rashmi Shan

Answered On : Jul 31st, 2006

To get 4th maximum valueSELECTMAx(salary)  FROM EMP WHERE SALARY NOT IN (SELECT top 3 quantity FROM EMP ORDER BY salary DESC)To get nth max valueMAx(salary)  FROM EMP WHERE SALARY NOT IN (SELECT top n-1 quantity FROM EMP ORDER BY salary DESC)

  
Login to rate this answer.
sheetalkamthe

Answered On : Apr 4th, 2007

Select top 1 salary from (select top 4 salary from tblEmployee order by asc)

  
Login to rate this answer.
Faizal

Answered On : Jul 6th, 2007

/*   Please try to enter the value of 'N' and you can retrieve that row*/select * from emp where sal=(select min(sal) as Salary from    (select sal from (select distinct sal from emp) order by sal desc) where rownum <= &n)

  
Login to rate this answer.
Amol Pawar

Answered On : Jul 16th, 2007

Select 5th max salarySELECT * FROM emp e1 WHERE (5 = (SELECT COUNT(DISTINCT (e2.sal))FROM emp e2 WHERE e2.sal >= e1.sal))

  
Login to rate this answer.
Lokesh.V

Answered On : Jul 20th, 2007

Hi Pls find the generalised Sql statement put  n value that it!select distinct (a.sal) from emp a where &n=(select count (distinct(b.sal)) from emp b where a.sal<=b.sal);it gives enter the value for n:

  
Login to rate this answer.

select * from table_name where (select rownum rn,sal from (select distinct sal From emp order by sal desc)) where rn between 4 and 5;

  
Login to rate this answer.
ghoshkunal123

Answered On : Jan 29th, 2008

View all answers by ghoshkunal123

Use Rank() or Dense_Rank() to make life easier.

  
Login to rate this answer.
mohd sajjad

Answered On : Jun 25th, 2008

View all answers by mohd sajjad

Select sal from(select sal from emp order by sal desc) where rownum<=4 minus select sal from(select sal from emp order by sal desc) where rownum<=3;

  
Login to rate this answer.
prateekin

Answered On : Oct 3rd, 2008

View all answers by prateekin

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

Answered On : Nov 1st, 2009

View all answers by s2r2v2n2n

Use the below query, this works fine to find the 4th and 5th maximum salary of an emp table. You can change the numbers in where condition to get the nth maximum.SELECT a.*, b.rn
FROM emp a, (SELECT emp_id, rownum rn FROM emp ORDER BY salary DESC) b
WHERE a.emp_id = b.emp_id
AND rn BETWEEN 4 AND 5;

  
Login to rate this answer.
satheyaraaj

Answered On : Jul 9th, 2011

View all answers by satheyaraaj

SELECT TOP 1 GrosspayFROM (SELECT DISTINCT TOP 4 GrosspayFROM SalaryMasterORDER BY Grosspay DESC) aORDER BY Grosspay

  
Login to rate this answer.
krishna

Answered On : Jul 17th, 2011

DB2:

Code
  1. SELECT DISTINCT sal FROM emp A WHERE n= (SELECT count(DISTINCT sal) FROM emp B
  2. WHERE A.sal <= B.sal)

  
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.