Re: Help with my sql

From: David Fitzjarrell <oratune_at_msn.com>
Date: 26 Mar 2002 11:26:53 -0800
Message-ID: <32d39fb1.0203261126.666739ef_at_posting.google.com>


I believe this returns a result set more to your liking:

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

COL1 COL2 COL3 MAX(COL4) ---------- ---------- ---------- ----------

a          b          c                   3
a          c          b                   1




"Viatcheslav Hitrov" <hitrov_at_promos.ru> wrote in message news:<a7q42m$5a4$1_at_octopus.co.ru>...
> Hi!
> select *
> from test
> where col4 = (select max(col4) from test)
>
> COL1 COL2 COL3 COL4
> ---------- ---------- ---------- ----------
> a b c 3
> is that you need?
> Group function is unnecessary here.
>
> > 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.
> >
> >
> >
> >
> >
> >
> > --
> > 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 - 20:26:53 CET

Original text of this message