Re: Help with my sql

From: crappy <crappygolucky_at_hotmail.com>
Date: 26 Mar 2002 11:27:59 -0800
Message-ID: <ce31c410.0203261127.1db0d57f@posting.google.com>

try this:

select col1, col2, col3, substr(max(stuff), 4, 4) from (select col1, col2, col3, col1||col2||col3||col4 stuff

      from test 
      order by col1,col2,col3,col4) 

group by col1, col2, col3

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 - 13:27:59 CST