Re: Help with my sql

Your query

SQL> select * from test
> 2 where col4 in (
> 3 select max(col4) from test
> 4 group by col1,col2,col3);

The subquery will return 1 and 3. The outer query will then select all rows where col4 is either 1 or 3 and that's all of them.

Try

select col1, col2, col3, max(col4)
from test
group by col1, col2, col3;

You get

a b c 3
a c b 1

> but when i
>
> >
> 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.
>
>
>
>
>
>
> --
> Sent by dbadba62 from hotmail piece from com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi
Received on Tue Mar 26 2002 - 10:43:20 CST