Joe Sunday wrote:
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
MySQL reports -
Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns
is illegal if there is no GROUP BY clause
I found one that actually returns the desired result - SQLite3.
sqlite> select * from test;
15|20
15|10
sqlite> select ycis_id,min(tindex),avg(tindex) from test where ycis_id=15;
15|10|15
sqlite>
--
Shane Ambler
[EMAIL PROTECTED]
Get Sheeky @ http://Sheeky.Biz
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
