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 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)
Login to rate this answer.
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)
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 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)
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 salary
SELECT * 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.
Use Rank() or Dense_Rank() to make life easier.
Login to rate this answer.
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.
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.
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.
SELECT TOP 1 Grosspay
FROM (
SELECT DISTINCT TOP 4 Grosspay
FROM SalaryMaster
ORDER BY Grosspay DESC) a
ORDER BY Grosspay
Login to rate this answer.
krishna
Answered On : Jul 17th, 2011
DB2:
Code
SELECT DISTINCT sal FROM emp A WHERE n= (SELECT count(DISTINCT sal) FROM emp B
WHERE A.sal <= B.sal)
Login to rate this answer.
