Re: Second highest value

From: Michel Cadot <micadot_at_altern.org>
Date: Tue, 10 Aug 1999 10:34:56 +0200
Message-ID: <7ooo8h$9hp$1@oceanite.cybercable.fr>

Here two queries, the first one gives you the 2 highest invoice date values in two rows and the second one these two values in the same row.

select dealid, customerid, accountid, max(invoicedate) from "view"
group by dealid, customerid, accountid
union
select dealid, customerid, accountid, max(invoicedate) from "view" a
where invoicedate < (select max(invoicedate) from "view" b

                     where b.dealid=a.dealid
                       and b.customerid = a.customerid
                       and b.accountid = a.accountid)

group by dealid, customerid, accountid
/

select a.dealid, a.customerid, a.accountid, a.mx highest, max(b.invoicedate) second
from "view" b,

     (select dealid, customerid, accountid, max(invoicedate) mx
      from "view" group by dealid, customerid) a
where b.dealid (+) = a.dealid
  and b.customerid (+) = a.customerid

  and b.accountid (+) = a.accountid
  and b.invoicedate (+) < a.mx
group by a.dealid, a.customerid, a.accountid, a.mx /

Outer join is necessary for groups with only one row.

raorg_at_my-deja.com a écrit dans le message <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.
>
>Thanks,
>Rajesh.
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Tue Aug 10 1999 - 03:34:56 CDT