Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: HELP : SQL Question

Re: HELP : SQL Question

From: Sergey Maruschenko <mara_at_sunbay.com>
Date: 2000/02/17
Message-ID: <38ABEEBC.4CAB2E33@sunbay.com>#1/1

"Joao Ricardo B. Oliveira" wrote:
>
> Hi there:
>
> ...
> id1 id2 id3 id4 id5 value(*) date
> 3043 1572 50 1101 1 10 2-9-98
> 3043 1572 20 1101 2 12 5-6-99
> ...
>
> A traditional MAX select
>
> select id1, id2, id3, id4, MAX(value)
> from table
> group by id1, id2, id3, id4;
>
> result :
>
> 3043 1572 20 1101 12
>
> But i need also the other columns ... date, id5 in the results....
>
> 3043 1572 20 1101 2 12 5-6-99
>
> I want to get the all row that as the high value(12) in a group by
> clause using only the primary keys id1,id2,id3,id4 and not using the id5.
>
> For instance :
>
> select id1,id2,id3,id4,id5, MAX(value), date
> from table
> group by id1,id2,id3,id4
>
> But this is not valid, because the all columns in the select aren't a
> GROUP BY expression...
>
> How can I have the ALL columns information with a group by clause using
> only some columns ???
>
> Please mail me...
>
> Thankx
>
> --
> ----------------------------------------------------------
> - Joao Ricardo B Oliveira jricardo_at_ipb.pt - Oracle DBA
> - EURO2004 http://welcome.to/euro2004
> - HOMEPAGE http://www.ipb.pt/~jricardo/myhome.swf
> ----------------------------------------------------------

Hi

You can try following statement:
  SELECT id1, id2, id3, id4, id5, value, date     FROM table
   WHERE (id1, id2, id3, id4, value) IN (

           SELECT id1, id2, id3, id4, MAX(value)
             FROM table
            GROUP BY id1, id2, id3, id4
         )
Received on Thu Feb 17 2000 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US