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

Showing Answers 1 - 17 of 17 Answers

Jayakumar M

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

  Was this answer useful?  Yes

Anonymous

  • 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

  Was this answer useful?  Yes

Elango

  • Jan 19th, 2006
 

4th MAX

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

5th MAX

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

  Was this answer useful?  Yes

pktelango

  • Jan 19th, 2006
 

4th MAx

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

5th MAx

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

Nth MAx

select max(id) from EMP A where N=( select count(id) From EMP  B where B.ID>=A.ID)

  Was this answer useful?  Yes

Rahman

  • Jul 17th, 2006
 

Pls Explain the query

  Was this answer useful?  Yes

rashmi Shan

  • Jul 31st, 2006
 

To get 4th maximum value

SELECT

MAx(salary)  FROM EMP WHERE SALARY NOT IN (SELECT top 3 quantity FROM EMP ORDER BY salary DESC)

To get nth max value

MAx(salary)  FROM EMP WHERE SALARY NOT IN (SELECT top n-1 quantity FROM EMP ORDER BY salary DESC)

  Was this answer useful?  Yes

sheetalkamthe

  • Apr 4th, 2007
 

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

  Was this answer useful?  Yes

Faizal

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

  Was this answer useful?  Yes

Amol Pawar

  • Jul 16th, 2007
 

Select 5th max salary

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

  Was this answer useful?  Yes

Lokesh.V

  • 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:

  Was this answer useful?  Yes

mohd sajjad

  • Jun 25th, 2008
 

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;

  Was this answer useful?  Yes

s2r2v2n2n

  • Nov 1st, 2009
 

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;


  Was this answer useful?  Yes

krishna

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

  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.

Answer Question

Click here to Login / Register your free account


 
Send   Reset

 

Related Answered Questions

 

Related Open Questions