Re: Second highest value

Too bad it's a view, otherwise this would have been very fast: create an index DATE_I on invoicedate

select /*+ INDEX_DESC (INVOICES DATE_I) */ * from invoices
where rownum<=2
order by invoicedate;

fumi wrote:
>
> <raorg_at_my-deja.com> wrote in message news:7ofdda$m8j$1_at_nnrp1.deja.com...
> > Hi all,
> > I have a view:
> > dealid,
> > customerid,
> > accountid,
> > invoicedate,
> > invoicenum,
> >
> > For group of dealid, customerid, accountid, How can I get the max
> > invoicedate and the second highest invoice date value. preferably in
> > one query, but two will definitely help.
>
> select a.invoicedate
> from view_name a, view_name b
> where a.invoicedate<=b.invoicedate
> group by a.sal
> having count(distinct b.invoicedate)<=2;

--

 Jorgen Bosman                      jbosman_at_village.uunet.be (home)       
 Ferdinand Verbieststraat 3 bus 5   jbosma_at_fercomsys.be   (work)       
 2030 Antwerpen, Belgium            http://gallery.uunet.be/jbosman

                                    ICQ: 5010996
***********************************************************************