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.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