I guess this problem is a bit underspecified. Do we want the second largest
value of all values or the second largest value of distinct values? The
queries below don't return the same answer if id contains duplicates.
"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message
news:amru8e$la$1_at_babylon.agtel.net...
> In my tests on 8.1.7.4,
>
> SELECT MIN(id) second_max FROM
> (SELECT id FROM
> (SELECT id FROM my_table ORDER BY id DESC)
> WHERE rownum <= 2
> )
>
> hit just two blocks of unique index on id (root and tail leaf, I guess)
> with INDEX FULL SCAN DESCENDING, then COUNT STOPKEY and
> SORT AGGREGATE on top of this.
>
> SELECT MAX(id) second_max
> FROM my_table
> WHERE id < (SELECT MAX(id) FROM my_table)
>
> visited four index blocks with INDEX RANGE SCAN (MIN/MAX),
> FIRST ROW and SORT AGGREGATE on top.
>
> SELECT id second_max FROM
> (SELECT id,
> rank() OVER (ORDER BY id DESC) rank
> FROM my_table
> )
> WHERE rank = 2
>
> was the worst because it hit all index blocks with INDEX FULL SCAN (in
> ascending sort order, while with the first query CBO was smart enough
> to choose reverse order to get to needed rows as fast as it could) and
> then used WINDOW SORT PUSHED RANK to get to the data. I think we
> could get results similar to those for the first query if CBO could choose
> descending index scan for the window, but for some reason it couldn't.
>
> --
> Vladimir Zakharychev (bob_at_dpsp-yes.com)
http://www.dpsp-yes.com
> Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet
applications.
> All opinions are mine and do not necessarily go in line with those of my
employer.
>
>
> "Brian E Dick" <bdick_at_cox.net> wrote in message
news:d63k9.23183$IL6.1312087_at_news2.east.cox.net...
> > What? You want performance, too? <g>
> >
> > I was a bit quick with the send button. How about
> >
> > select max(c) from t where c < (select max(c) from t)
> >
> > What effect would a descending index on c make? Can the database resolve
the
> > outer max without totally enumerating all the values of c less than the
> > inner max? Does the explain plan prove or disprove this? Or do you have
to
> > infer it from TKPROF IO counts?
> >
> > "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> > news:3D908DB2.A32D71FA_at_exesolutions.com...
> > > Brian E Dick wrote:
> > >
> > > > select max(c) from (select c from t where c < (select max(c) from
t))
> > > >
> > > > "abhishek" <singhhome_at_yahoo.com> wrote in message
> > > > news:e52380ab.0209232324.24bd2cbd_at_posting.google.com...
> > > > > how to write sql querry to
> > > > > 1. show the second maximum value of a column.
> > >
> > > Fascinating. I like it.
> > >
> > > Now to explain plan them all.
> > >
> > > Thanks everyone for the material. My students, in a few weeks, will
thank
> > > you too. (I think)
> > >
> > > Daniel Morgan
> > >
> >
> >
Received on Thu Sep 26 2002 - 06:02:39 CDT
