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:[EMAIL PROTECTED]
Sent: Tuesday, 23 March 2004 16:17
To: David Witham
Cc: [EMAIL PROTECTED]
Subject: Re: [SQL] Sorting an aggregated column
"David Witham" <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
