Re: This is how U can find out the 'N'th maximum value from a table by sql

Why not just use an analytic function? They are more versatile, less performance overhead, and less coding (row_number,rank,dense_rank,etc).

Tom: the RANK function IS for selecting the Top N in the list, however you'd like to determine them. Good in 8.1.6 and up.

Tom McClelland wrote:
>
> Like it. I love the Oracle use of embedded queries like this. Now,
> what if I want the nth percentile of column_1's distribution..... Some
> db's have select top n percent. Can a variation of your technique be
> used to solve the problem in Oracle? It feels like it should be
> possible by adding in some more "select count(*)" subqueries but I
> haven't tried it yet.
>
> Regards
>
> > Try out this generalised query which can fetch Nth maxim value from a
> > table!
> > Your comments are welcome along with variations of this you may creat
> >
> > select min(COLUMN_1) from (select COLUMN_1 from NEEDEDTABLE_ta order
> > by
> > COLUMN_1 DESC) where rownum <= N;
> >
> > where N should be substituted for the Nth maximum value you need to
> > fetch out from the table.
> >
> >
> > Cheers
> > Sudhakaran.K
> > NCST
> > BANGALORE/India
> >
> > > sohelcsc_at_yahoo.com (Leader) wrote in message news:<b1a93c73.0202132336.4191d0e6_at_posting.google.com>...
> > > > Hi all,
> > > > How can i find out the second maximum value from a table by sql.
> > > >
> > > >
> > > > thanks
> > > > Hoque
> > >
> > > Hi there,
> > >
> > > Try following SQL code
> > >
> > > select max(field) from table
> > > where field not in (select max(field) from table);
> > >
> > > You will get second highest value
> > >
> > > Ashwani
Received on Tue Feb 19 2002 - 16:11:44 CET