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.66rows=100 width=45) -> Group (cost=122.16..127.16 rows=1000 width=45) -> Sort
(cost=122.16..124.66rows=1000 width=45) Sort Key: survey -> Subquery Scan dws
(cost=69.83..72.33rows=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
sortby 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
reorderany 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@sss.pgh.pa.us]
Sent: Tuesday, 23 March 2004 16:17
To: David Witham
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Sorting an aggregated column
"David Witham" <davidw@unidial.com.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