HELP : SQL Question

From: Joao Ricardo B. Oliveira <jricardo_at_ipb.pt>
Date: 2000/02/17
Message-ID: <88gof4$s71$1@pan.ipb.pt>#1/1

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

----------------------------------------------------------

Received on Thu Feb 17 2000 - 00:00:00 CST