n th maximum value

[Hari Mohan]

Hi All .,

This is one of the most frequently asked question but still the less
frequently answered one !!!


In Oracle .,
max() function returns the highest value in a column
how to find the 2nd ,3rd ,6th or nth maximum value in a Column...
Using Order By displays All the cells as a Set.
But .,how to get the individual cell itself ???




-HariMohan

[Shailendra shail]

Yes Hari, This is common question.. and we have different answer.. lets see how many answer we can collect..

I'd like to invite to mr. Abhijeet (as he is working in DB domain) to answer this question. Even i'd be happy to see individual answer for all the participants.. !!

-Shail

[Nishant Gupta]

Substitute the number in place of "&n" i.e if you want 6th maximum substitute 6 in place of "&n", and this query should work...

select * from table_name r1

where &n = (select count(*) from vendor_details

where desired_column >= r1.desired_column);

Thanks & Regards,

Nishant Gupta,

Developer (TCS-Apple)

✉  nishan...@apple.com

( neo324893 @ AIM )

( +91 8050041001, 9620779208 )

[Abhijeet Kumar]

==================================================================================
Hi,

You can use the following querry to find the Nth Max salary from Employee table.


Select Salary From Employee E1 Where
    (N-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
        E2.Salary > E1.Salary);

================================================================================

--

With Regards,

Abhijeet Kumar|Software Engineer|Bangalore|

Office : 8041068296

Mobile : +91-9342038171

Email   : abhijeet....@gmail.com

A R I C E N T

[Shailendra shail]

can we make use of oracle new feature "CONNECT BY PRIOR" or "CONNECT BY LEVE" clause ?

if so, we can achieve this

select level, max(sal) from scott.emp
where level = '&n'
connect by prior (sal) > sal
group by level;

(remember this may not work in OracleVersion <9i )

Note: To know more about connect by level visit this link :
http://www.orafaq.com/wiki/Oracle_Row_Generator_Techniques

[Abhijeet Kumar]

Hi Nishant,


Your query is not working when i am assigning N=1 But it is working fine from 2 onwards....

[Abhimanyu Singh]

Hi,
     Below query also fetch desired result :-

             select distinct(SAL) from (select SAL,dense_rank() over (order by SAL desc) ranking from test ) where ranking=n;

i executed query posted by nishant  and getting different result if two or more salary are equals.

Table Name :- test
ID      SAL
1        100
2        200
3        500
4        300
5        600
6        700
7        500
8        600


I executed below query :-
         select * from test r1 where 2 = (select count(*) from test where SAL >= r1.SAL); // No result found
         select * from test r1 where 3 = (select count(*) from test where SAL >= r1.SAL);// fetches result      ID      SAL
                                                                                                                                                                  5        600
                                                                                                                                                                  8        600

Regards,
Abhimanyu.

[vijay kumar]

Hi All

I thought it's correct answer for you question .

For particular example of employee :

How to get 1st, 2nd, 3rd, 4th, nth topmost salary from an Employee table

The following solution is for getting 6th highest salary from Employee table ,

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 6 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary

You can change and use it for getting nth highest salary from Employee
table as follows

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
where n > 1 (n is always greater than one)

Regards
Vijayakumar .

On 8 June 2011 15:35, Abhimanyu Singh <abhiv...@gmail.com> wrote:
>
> Hi,
>      Below query also fetch desired result :-
>
>              select distinct(SAL) from (select SAL,dense_rank() over (order by SAL desc) ranking from test ) where ranking=n;
>
> i executed query posted by nishant  and getting different result if two or more salary are equals.
>
> Table Name :- test
> ID      SAL
> 1        100
> 2        200
> 3        500
> 4        300
> 5        600
> 6        700
> 7        500
> 8        600
>
>
> I executed below query :-
>          select * from test r1 where 2 = (select count(*) from test where SAL >= r1.SAL); // No result found

[Hari Mohan]

Hi All.,

Its Nice that there  are number of queries to solve de problem.Great...!!!

But .,
The logic that goes after the cartesian join remains "UNExplained"...
Can Any one give a clear picture ..????



-HariMohan

[Hari Mohan]

Hi All ,

I worked out this query.

select min(SALARY) from (select SALARY,rownum from EMPLOYEE order by
SALARY desc) where rownum <=N

Replace N with +ve integer to find the n the maximum value.
Problem is :
If repeated values are found .,then distinct(salary),rownum in the
subquery does not work !!!!!
Any Clues...????