Help with my sql

From: Mike F <u518615722_at_spawnkill.ip-mobilphone.net>
Date: Tue, 26 Mar 2002 15:04:24 GMT
Message-ID: <l.1017155064.1589172363_at_[64.94.198.252]>

I have a test table
SQL> select * from test;

COL1 COL2 COL3 COL4
---------- ---------- ---------- ----------

[Quoted] [Quoted] [Quoted] 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 (
[Quoted]   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

[Quoted] I still got two unwanted rows, which is row no1 and row no2.

How could i eliminate the first two rows?

[Quoted] 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/content/new

Received on Tue Mar 26 2002 - 16:04:24 CET