Re: find the second largest element

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 20 Nov 2000 23:11:21 -0000
Message-ID: <974758121.20629.0.nnrp-14.9e984b29@news.demon.co.uk>

You have a bit of a problem there
when deciding what to do about
ties. However, assuming you are
interested in largest and second largest values, irrespective of how many appear
for each salary, and if you have 8.1.6
then something like this might work.
(I don't have an instance up at the moment, so may have a couple of errors in here; sorry)

select

    id,
    max(decode(sal_place,1,sal,null)) top_sal,     max(decode(sal_place,2,sal,null)) next_sal, from
(
select

    id, sal,
    dense_rank() over (

            partition by id
            order by sal desc

    ) as sal_place
from

    id_sal_table
)
where sal_place <= 2
group by id
;

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison Wesley Longman
Book bound date: 8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
Order from Amazon via: http://www.jlcomp.demon.co.uk

Hongjiew wrote in message <20001120164537.17295.00000947_at_ng-cg1.aol.com>...


>Dear friend, I like to write a query that will find the largest and the

 second


>largest values of a column in  a group by setting.

>

>For example,

>

>Table a

>

>id      sal

>001   1000

>001   1000

>001   500

>002  200

>002 400

>002  500

>

>...

>I want to get

>id maxsal secmaxsal

>001 1000  1000

>002 500   400.

>

>Thanks.

>

>hongjiew_at_aol.com

Received on Mon Nov 20 2000 - 17:11:21 CST