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

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

Reply via email to