Re: Help with my sql

yes, i am insane. the simple group by that other posters suggested seems to do what you are looking for. i should learn to read.

crappygolucky_at_hotmail.com (crappy) wrote in message news:<ce31c410.0203261127.1db0d57f_at_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 - 22:44:42 CET