How to write a SQL query to find N'th largest element in a Column???

vinodhsamurai

 Profile Answers by vinodhsamurai 

• Jan 11th, 2007

 

Consider the following table
create table table1(a number);
insert into table1 values(5);
insert into table1 values(23);
insert into table1 values(8);
insert into table1 values(99);
insert into table1 values(99);
commit;
To retrieve the Nth largest number :
========================
Lets say we want to find the 2nd largest number
1)There are two interpretations for the this.
When we consider the distinct values , then 23 is the second largest number.
So  to find the absolute second largest number ,the following query can be used :
select a
from
(
select a , rank() over (order by a desc) rank from
(select distinct(a) from table1)
)
where  rank=2;
2) If we are concerned about true ranking
ie equal values have equal ranks then the following query can be used:(In this case there will be no rank 2)
 select a
 from
 (select a, rank() over (order by a desc) rk from table1)
 where rk=2;
Cheers
Vinodh

sanjith

• Jan 24th, 2007

 

try this outselect distinct(a.C.N) from T.N a where &n=(select count(b.C.N) from T.N b where a.C.N <= b.C.N)C.N == Column nameT.N == Table namea & b == alias name&n == nth largest element

SHEEL2006

• Feb 6th, 2007

 

select  a.salary from <tabalename a >

where &n=( select  count(distinct b.salary) 

 from <table name b> where a.salary<=b.salary;

this quarry gives u 1st,2nd,3rd,4th............................ higest salary but here I use only copy of 1st table in statement select  count(distinct b.salary)  from <tablename b) but one more interesting  thing is that u can also find the 1st,2nd,3rd.............. min salary also with this quarry 

select  a.salary from <tabalename a   > where &n=( select  count(distinct b.salary)  from <table name b>  where a.salary<b.salary;      

 donot use topN-analysis 

 

Radhi

• Feb 15th, 2007

 

if u need nth row in a table try this---select rownum,ename,eno from emp group by rownum,ename,eno having rownum>=nminusselect rownum,ename,eno from emp group by rownum,ename,eno having rownum>n/

madhuk17

 Profile Answers by madhuk17 Questions by madhuk17

• Feb 17th, 2007

 

select * from emp a where &n=(select count(distinct(sal)) from emp b
where a.sal<=b.sal);

deepajagan

 Profile Answers by deepajagan 

• Mar 27th, 2007

 

hai try this

if we want to second largest number

select * from (select ROWNUM R,e.* from employee e order by emp_sal desc) where R=2;

hindol_saha

 Profile Answers by hindol_saha Questions by hindol_saha

• Mar 27th, 2007

 

select a frrom table1 where n=&n
connect by prior a>a;

Samuel Edison

• Mar 27th, 2007

 

Try this

select max(sal) from emp where sal< (select max(sal)from emp)

rambabu12

 Profile Answers by rambabu12 

• Nov 26th, 2007

 

Hi madhuk17
Could you please explain how this query works?

select * from emp a where &n=(select count(distinct(sal)) from emp b
where a.sal<=b.sal);

say for example i substitute value 4?

Thanks in advance,
Ram.

mahesh_khadse

 Profile Answers by mahesh_khadse 

• May 24th, 2008

 

There are many ways of doing these but the most supper that i like is.......


syntax : 

Select * from TableName A where Nth=(select count(*) from TableName B where A.ColumnName<=B.ColumnName)

Ex:

suppose u want a employee who has a 3rd largest salary then the query will be....

 
Select * from employee a where 3=(select count(*) from employee b where a.salary<=b.salary)

asish_cse

 Profile Answers by asish_cse 

• Aug 15th, 2009

 

Consider the table-->
table (a number);
select a
from
(
select a rank() over (order by a desc) rank
from
select distinct (a) from table
)
where rank 1;

sure_prince

 Profile Answers by sure_prince Questions by sure_prince

• Aug 18th, 2009

 

SELECT level,max(sal) FROM emp

WHERE level=&Givlevel

CONNECT BY PRIOR sal>sal 

GROUP BY level;

This gives the n'th highest salary from emp table.

ajeeteng

 Profile Answers by ajeeteng 

• Mar 23rd, 2010

 

SELECT salary FROM employees E1
WHERE (3) = (SELECT COUNT(DISTINCT(E2.salary))
FROM employees E2
WHERE E1.salary < E2.salary)

with this query you can find any nth highest value......

adamzg

 Profile Answers by adamzg 

• Dec 21st, 2010

 

This is how you would do this in Oracle...

select
  *
from
  (
    select
      rownum n,
      size_query.*
    from
    (
      select
        scalar_column
      from
        the_table
      order by
        scalar_column desc
    ) size_query
  )
where
  n = 2
;

...you can also apply this to row counts...

select
*
from
(
select
rownum n,
count_query.*
from
(
select
identifier,
count(*) the_count
from
some_table
group by identifier
order by count(*) desc
) count_query
)
where
n = 2
;

...finds the 2nd largest count.

nirmal1in

 Profile Answers by nirmal1in Questions by nirmal1in

• Dec 25th, 2010

 

Say for example below are data exist on EMP table

EMPNO  EMPNAME                DEPTNO          SALARY

----------------------------------------------------------------

1           EmpName - 1          10                   1000
2           EmpName - 2          10                   11000
3           EmpName - 3          11                   11000
4           EmpName - 4          11                   11000
5           EmpName - 5          10                   12000
6           EmpName - 6          11                   13000
7           EmpName - 7          11                   14000
----------------------------------------------------------------

Below is query to get the nth largest salary and employee details from table:

select * from

(select empno EMPNO,empname,salary salary,dense_rank() over (order by salary desc) rankfrom

emp)

where rank = n

Replace n with required number

example :

select * from(select

empno EMPNO,empname,salary salary,dense_rank() over (order by salary desc) rank

from emp)

where rank = 4

We will get following results based on above data:

EMPNO   EMPNAME     SALARY   RANK

---------------------------------
2       EmpName - 2 11000    4
3       EmpName - 3 11000    4
4       EmpName - 4 11000    4
---------------------------------

sampra

 Profile Answers by sampra 

• Mar 6th, 2012

 

select * from hello h where 1=(select count(discnt amt) from hello where h.amt