From: David Witham <davidw@unidial.com.au>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Sorting an aggregated column
Date: Tue, 23 Mar 2004 18:17:51 +1100
Message-ID: <CFA248776934FD43847E740E43C346D199DCAC@ozimelb03.ozicom.com> (raw)
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@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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: davidw@unidial.com.au, tgl@sss.pgh.pa.us
Subject: Re: Sorting an aggregated column
In-Reply-To: <CFA248776934FD43847E740E43C346D199DCAC@ozimelb03.ozicom.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox