Mark Woodward wrote:
Stephen Frost wrote:
select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15;
But back to the query the issue comes in that the ycis_id value is
included with the return values requested (a single row value with
aggregate values that isn't grouped) - if ycis_id is not unique you will
get x number of returned tuples with ycis_id=15 and the same min() and
avg() values for each row.
Removing the ycis_id after the select will return the aggregate values
you want without the group by.
I still assert that there will always only be one row to this query. This
is an aggregate query, so all the rows with ycis_id = 15, will be
aggregated. Since ycis_id is the identifying part of the query, it should
not need to be grouped.
SELECT ycis_id FROM table WHERE ycis_id=15; returns a single tuple when
ycis_id is unique otherwise multiple tuples
which means that SELECT ycis_id is technically defined as returning a
multiple row tuple even if ycis_id is unique - the data in the tuple
returned is data directly from one table row
SELECT max(col2) FROM table WHERE ycis_id=15; returns an aggregate tuple
SELECT ycis_id FROM table WHERE ycis_id=15 GROUP BY ycis_id; returns an
aggregate tuple (aggregated with the GROUP BY clause making the ycis_id
after the SELECT an aggregate as well)
You can't have both a single tuple and an aggregate tuple returned in
the one statement. If you want the column value of ycis_id in the
results you need the group by to unify all returned results as being
aggregates.
--
Shane Ambler
[EMAIL PROTECTED]
Get Sheeky @ http://Sheeky.Biz
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
