Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Help with my sql
If I understand correctly...
What you want will be returned by this:
select col1, col2, col3, max(col4)
from test
group by col1, col2, col3
JP
u518615722_at_spawnkill.ip-mobilphone.net (Mike F) wrote in message news:<l.1017155064.1589172363@[64.94.198.252]>...
> I have a test table
> SQL> select * from test;
>
> COL1 COL2 COL3 COL4
> ---------- ---------- ---------- ----------
> a b c 1
> a b c 1
> a b c 2
> a b c 3
> a c b 1
>
> For each combination of col1,col2, and col3, I just want the one with
> the max(col4).
>
> but when i
>
> SQL> select * from test
> 2 where col4 in (
> 3 select max(col4) from test
> 4 group by col1,col2,col3);
>
> COL1 COL2 COL3 COL4
> ---------- ---------- ---------- ----------
> a b c 1
> a b c 1
> a c b 1
> a b c 3
>
> I still got two unwanted rows, which is row no1 and row no2.
>
> How could i eliminate the first two rows?
>
> Thanks for your help.
Received on Tue Mar 26 2002 - 12:35:31 CST