Re: HELP : SQL Question

A copy of this was sent to "Joao Ricardo B. Oliveira" <jricardo_at_ipb.pt> (if that email address didn't require changing) On Thu, 17 Feb 2000 12:01:30 -0000, you 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
>

select id1, id2, id3, id4, id5, value, date   from table t1
 where ( id1, id2, id3, id4, value ) = ( select id1, id2, id3, id4, max(value)

                                           from table t2
                                          where t2.id1 = t1.id1
                                            and t2.id2 = t1.id2
                                            and t2.id3 = t1.id3
                                            and t2.id4 = t1.id4 
                                          group by id1, id2, id3, id4 )

is one way (do NOT expect id1...id4 to be unique in the result set though as you would with a group by!! consider if the table has data such as:

id1 id2 id3 id4 value

1     1   1     1     100
1     1   1     1     100

There are 2 rows in this table with MAX(VALUE) for the vlaues (1,1,1,1).

> 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

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation