Re: Sorting an aggregated column

Hi Tom,

Thanks for the advice. I was planning to upgrade from 7.3.2 to 7.4 soon but this adds a bit more impetus.

Under 7.3.2 I rewrote the query as your example suggested:

explain select survey, list (
case when answer_bool = 't' then 'y'::varchar
when answer_bool = 'f' then 'n'::varchar
when answer_int is not null then answer_int::varchar
when answer_char is not null then answer_char::varchar
end
) as answers
from (select survey, answer_bool, answer_int, answer_char from dw_survey order by survey,question)
as dws
group by survey
order by survey;

--------------------------------------------------------------------------------------------
Aggregate (cost=122.16..129.66 rows=100 width=45)
-> Group (cost=122.16..127.16 rows=1000 width=45)
-> Sort (cost=122.16..124.66 rows=1000 width=45)
Sort Key: survey
-> Subquery Scan dws (cost=69.83..72.33 rows=1000 width=45)
-> Sort (cost=69.83..72.33 rows=1000 width=45)
Sort Key: survey, question
-> Seq Scan on dw_survey (cost=0.00..20.00 rows=1000 width=45)

So I see that there is the extra sort above the sub-query that wouldn't be there using 7.4. Are you saying that the sort by survey after the sort by survey,question would potentially reorder the records initially sorted by survey,question? If the sub-query had already sorted by survey (along with question), would the sort by survey bother to reorder any of the rows? E.g. if the subselect returned (assuming 1 answer from the 3 answer columns):
1,t
1,f
1,t
1,123
1,21
1,1
2,t
2,t
2,t
2,3
2,2
2,1
would the sort by survey potentially reorder these rows even though they don't need to be?

Regards,
David

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Tuesday, 23 March 2004 16:17
To: David Witham
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Sorting an aggregated column

"David Witham" <davidw(at)unidial(dot)com(dot)au> writes:
> This output is correct in this case but there is no guarantee that the
> answers will come out in "question" order. I can't see how to
> incorporate sorting by the "question" column using this approach.

As of PG 7.4 you can reliably use a sorted sub-select to determine the
order of inputs to a user-defined aggregate function. See for instance
http://archives.postgresql.org/pgsql-general/2003-02/msg00917.php

regards, tom lane