Re: how do I select in a group by if i want to handle stringsfunctions (first or last occurence)

From: IB <irb61_at_yahoo.com>
Date: Wed, 19 May 2004 12:01:24 -0400
Message-ID: <fLLqc.39$j04.35@news.oracle.com>

You can try this:
SELECT DISTINCT f1,avg(f2) ,f3 ,f4,max(f5)   FROM table1 a,(

          SELECT f1,avg(f2) ,max(f5)
          FROM table1
          WHERE
          GROUP BY f1) b

 WHERE a.f1 = b.f1
 ORDER BY 1
/

It may be very helpful if f1 is the primary key.

Hope this helps.
IB

igse_at_bluewin.ch wrote:

>lets say I have table t with fields t.f1 varchar (10)
> t.f2 number
> t.f3
>varchar(10)
> t.f4
>varchar(10)
> t.f5 number
>
>
>I want to make a select like:
>
>select f1, avg(f2) ,f3 ,f4,max(f5) from t group by f1 ;
>
>This will not work because I would need to group this thing with f3 and f4
>too.
>
>But I want to just have the first occurence or last occurence of f3 resp. f4
>and
>do not want to group having more than one entry in my results.
>What functions can I use to control f3 and f4 ?
>igse_at_bluewin.ch
>
>
>
>
Received on Wed May 19 2004 - 11:01:24 CDT