content-class: urn:content-classes:message
Subject: Re: Sorting an aggregated column 
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Date: Tue, 23 Mar 2004 18:17:51 +1100
Message-ID: <CFA248776934FD43847E740E43C346D199DCAC@ozimelb03.ozicom.com>
Thread-Topic: [SQL] Sorting an aggregated column 
Thread-Index: AcQQlhiz310A5xYJSZiMUftqCOab2wADlO+w
From: "David Witham" <davidw@unidial.com.au>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: <pgsql-sql@postgresql.org>

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 =3D 't' then 'y'::varchar
        when answer_bool =3D '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 or=
der by survey,question)
as dws
group by survey
order by survey;

---------------------------------------------------------------------------=
-----------------
 Aggregate  (cost=3D122.16..129.66 rows=3D100 width=3D45)
   ->  Group  (cost=3D122.16..127.16 rows=3D1000 width=3D45)
         ->  Sort  (cost=3D122.16..124.66 rows=3D1000 width=3D45)
               Sort Key: survey
               ->  Subquery Scan dws  (cost=3D69.83..72.33 rows=3D1000 widt=
h=3D45)
                     ->  Sort  (cost=3D69.83..72.33 rows=3D1000 width=3D45)
                           Sort Key: survey, question
                           ->  Seq Scan on dw_survey  (cost=3D0.00..20.00 r=
ows=3D1000 width=3D45)

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 s=
urvey,question would potentially reorder the records initially sorted by su=
rvey,question? If the sub-query had already sorted by survey (along with qu=
estion), would the sort by survey bother to reorder any of the rows? E.g. i=
f 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 do=
n'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=20


"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