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

--Joe

-- 
Joe Sunday <sunday@csh.rit.edu>  http://www.csh.rit.edu/~sunday/
Computer Science House, Rochester Inst. Of Technology