Re: Syntax bug? Group by?

> On Tue, Oct 17, 2006 at 02:41:25PM -0400, Mark Woodward wrote:
>
>> The output column "ycis_id" is unabiguously a single value with regards
>> to
>> the query. Shouldn't PostgreSQL "know" this? AFAIR, I think I've used
>> this
>> exact type of query before either on PostgreSQL or another system, maybe
>> Oracle, and it did work.
>
> Doesn't work in Oracle 10g:
>
> SELECT ycis_id, tindex from x where ycis_id = 15;
> YCIS_ID  TINDEX
> =======  ======
>      15      10
>      15      20
>
> SELECT ycis_id, min(tindex), avg(tindex) from x where ycis_id = 15;
> ORA-00937: not a single-group group function
>
> SELECT ycis_id, min(tindex), avg(tindex) from x where ycis_id = 15 GROUP
> BY ycis_id;
> YCIS_ID  MIN(TINDEX)  AVG(TINDEX)
> =======  ===========  ===========
>      15           10           15


That's interesting. I am digging through the SQL99 spec, and am trying to
find a definitive answer.