pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSorting items in aggregate function
5+ messages / 5 participants
[nested] [flat]
* Sorting items in aggregate function
@ 2006-09-12 15:46 Steven Murdoch <psql+Steven.Murdoch@cl.cam.ac.uk>
2006-09-12 18:17 ` Re: Sorting items in aggregate function Michael Fuhr <mike@fuhr.org>
2006-09-12 18:24 ` Re: Sorting items in aggregate function Volkan YAZICI <yazicivo@ttnet.net.tr>
2006-09-12 20:54 ` Re: Sorting items in aggregate function Osvaldo Rosario Kussama <osvaldo_kussama@yahoo.com.br>
0 siblings, 3 replies; 5+ messages in thread
From: Steven Murdoch @ 2006-09-12 15:46 UTC (permalink / raw)
To: pgsql-sql
I would like to concatenate sorted strings in an aggregate function. I
found a way to do it without sorting[1], but not with.
Here is an example of a setup and what I could like to achieve. Does
anyone have suggestions on what is the best way to get the desired
result?
Thanks,
Steven.
CREATE TABLE a ( -- Names
id INT PRIMARY KEY,
name TEXT NOT NULL);
CREATE TABLE b ( -- Codes
id INT PRIMARY KEY,
code CHAR(2) NOT NULL);
CREATE TABLE ab ( -- m:n relationship between a and b
id SERIAL PRIMARY KEY,
a_id INT NOT NULL,
b_id INT NOT NULL);
COPY a(id,name) FROM STDIN DELIMITER '|';
1|Alice
2|Bob
3|Charlie
\.
COPY b(id, code) FROM STDIN DELIMITER '|';
1|a
2|b
3|c
4|d
\.
COPY ab(a_id, b_id) FROM STDIN DELIMITER '|';
2|4
2|1
3|2
3|3
\.
-- Custom aggregate function which concatenates strings
CREATE AGGREGATE concat (
BASETYPE = text,
SFUNC = textcat,
STYPE = text,
INITCOND = '',
);
-- Current query
SELECT a.name, TRIM(CONCAT(b.code||' ')) AS codes
FROM
a LEFT JOIN ab ON (a.id=ab.a_id)
LEFT JOIN b ON (ab.b_id=b.id)
GROUP BY a.name
ORDER BY codes;
-- Actual output:
--
-- name | codes
-- ---------+-------
-- Alice |
-- Charlie | b c
-- Bob | d a
-- Desired output:
--
-- name | codes
-- ---------+--------
-- Alice |
-- Bob | a d
-- Charlie | b c
[1] http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html
--
w: http://www.cl.cam.ac.uk/users/sjm217/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sorting items in aggregate function
2006-09-12 15:46 Sorting items in aggregate function Steven Murdoch <psql+Steven.Murdoch@cl.cam.ac.uk>
@ 2006-09-12 18:17 ` Michael Fuhr <mike@fuhr.org>
2006-09-12 20:37 ` Re: Sorting items in aggregate function Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 5+ messages in thread
From: Michael Fuhr @ 2006-09-12 18:17 UTC (permalink / raw)
To: Steven Murdoch <psql+Steven.Murdoch@cl.cam.ac.uk>; +Cc: pgsql-sql
On Tue, Sep 12, 2006 at 04:46:28PM +0100, Steven Murdoch wrote:
> Here is an example of a setup and what I could like to achieve. Does
> anyone have suggestions on what is the best way to get the desired
> result?
Use the aggregate over an ordered subquery:
SELECT name, trim(concat(code || ' ')) AS codes
FROM (
SELECT a.name, b.code
FROM a
LEFT JOIN ab ON a.id = ab.a_id
LEFT JOIN b ON ab.b_id = b.id
ORDER BY b.code
) AS s
GROUP BY name
ORDER BY name;
Here's a comment from Tom Lane, one of the core developers, on
feeding the aggregate based on the subquery's order:
http://archives.postgresql.org/pgsql-general/2005-09/msg00047.php
--
Michael Fuhr
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sorting items in aggregate function
2006-09-12 15:46 Sorting items in aggregate function Steven Murdoch <psql+Steven.Murdoch@cl.cam.ac.uk>
2006-09-12 18:17 ` Re: Sorting items in aggregate function Michael Fuhr <mike@fuhr.org>
@ 2006-09-12 20:37 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2006-09-12 20:37 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Steven Murdoch <psql+Steven.Murdoch@cl.cam.ac.uk>; pgsql-sql
Michael Fuhr <mike@fuhr.org> writes:
> On Tue, Sep 12, 2006 at 04:46:28PM +0100, Steven Murdoch wrote:
>> Here is an example of a setup and what I could like to achieve. Does
>> anyone have suggestions on what is the best way to get the desired
>> result?
> Use the aggregate over an ordered subquery:
> SELECT name, trim(concat(code || ' ')) AS codes
> FROM (
> SELECT a.name, b.code
> FROM a
> LEFT JOIN ab ON a.id = ab.a_id
> LEFT JOIN b ON ab.b_id = b.id
> ORDER BY b.code
> ) AS s
> GROUP BY name
> ORDER BY name;
Note that if you need to GROUP in the outer query, it's best to sort the
inner query's output first by the outer query's grouping:
SELECT name, trim(concat(code || ' ')) AS codes
FROM (
SELECT a.name, b.code
FROM a
LEFT JOIN ab ON a.id = ab.a_id
LEFT JOIN b ON ab.b_id = b.id
ORDER BY a.name, b.code
^^^^^^^^^^^^^^
) AS s
GROUP BY name
ORDER BY name;
This way will still work if the planner decides to use a GroupAggregate
(which in fact it probably will, if it sees it can avoid another sort
step). The way Michael showed will only work if the plan uses
HashAggregate --- if the planner decides it needs Sort+GroupAggregate
in the outer query, the re-sort will probably destroy the ordering
by b.code.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sorting items in aggregate function
2006-09-12 15:46 Sorting items in aggregate function Steven Murdoch <psql+Steven.Murdoch@cl.cam.ac.uk>
@ 2006-09-12 18:24 ` Volkan YAZICI <yazicivo@ttnet.net.tr>
2 siblings, 0 replies; 5+ messages in thread
From: Volkan YAZICI @ 2006-09-12 18:24 UTC (permalink / raw)
To: Steven Murdoch <psql+Steven.Murdoch@cl.cam.ac.uk>; +Cc: pgsql-sql
On Sep 12 04:46, Steven Murdoch wrote:
> I would like to concatenate sorted strings in an aggregate function. I
> found a way to do it without sorting[1], but not with.
If the array elements will be made of integers, then you can use sort()
procedure comes with intarray contrib module. For instance,
SELECT concat(T.sorted_arr)
FROM (SELECT sort(arr) FROM tbl) AS T (sorted_arr);
If related column will also include text values, you can create a
suitable sort() procedure for text[] type and use it instead.
Regards.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Sorting items in aggregate function
2006-09-12 15:46 Sorting items in aggregate function Steven Murdoch <psql+Steven.Murdoch@cl.cam.ac.uk>
@ 2006-09-12 20:54 ` Osvaldo Rosario Kussama <osvaldo_kussama@yahoo.com.br>
2 siblings, 0 replies; 5+ messages in thread
From: Osvaldo Rosario Kussama @ 2006-09-12 20:54 UTC (permalink / raw)
To: Steven Murdoch <psql+Steven.Murdoch@cl.cam.ac.uk>; pgsql-sql
Steven Murdoch escreveu:
> I would like to concatenate sorted strings in an aggregate function. I
> found a way to do it without sorting[1], but not with.
>
> Here is an example of a setup and what I could like to achieve. Does
> anyone have suggestions on what is the best way to get the desired
> result?
>
> Thanks,
> Steven.
>
> CREATE TABLE a ( -- Names
> id INT PRIMARY KEY,
> name TEXT NOT NULL);
>
> CREATE TABLE b ( -- Codes
> id INT PRIMARY KEY,
> code CHAR(2) NOT NULL);
>
> CREATE TABLE ab ( -- m:n relationship between a and b
> id SERIAL PRIMARY KEY,
> a_id INT NOT NULL,
> b_id INT NOT NULL);
>
> COPY a(id,name) FROM STDIN DELIMITER '|';
> 1|Alice
> 2|Bob
> 3|Charlie
> \.
>
> COPY b(id, code) FROM STDIN DELIMITER '|';
> 1|a
> 2|b
> 3|c
> 4|d
> \.
>
> COPY ab(a_id, b_id) FROM STDIN DELIMITER '|';
> 2|4
> 2|1
> 3|2
> 3|3
> \.
>
> -- Custom aggregate function which concatenates strings
> CREATE AGGREGATE concat (
> BASETYPE = text,
> SFUNC = textcat,
> STYPE = text,
> INITCOND = '',
> );
>
> -- Current query
> SELECT a.name, TRIM(CONCAT(b.code||' ')) AS codes
> FROM
> a LEFT JOIN ab ON (a.id=ab.a_id)
> LEFT JOIN b ON (ab.b_id=b.id)
> GROUP BY a.name
> ORDER BY codes;
>
> -- Actual output:
> --
> -- name | codes
> -- ---------+-------
> -- Alice |
> -- Charlie | b c
> -- Bob | d a
>
>
> -- Desired output:
> --
> -- name | codes
> -- ---------+--------
> -- Alice |
> -- Bob | a d
> -- Charlie | b c
>
> [1] http://www.postgresql.org/docs/8.0/interactive/sql-createaggregate.html
>
Look this message:
http://archives.postgresql.org/pgsql-sql/2006-05/msg00044.php
[]s
Osvaldo
_______________________________________________________
Você quer respostas para suas perguntas? Ou você sabe muito e quer compartilhar seu conhecimento? Experimente o Yahoo! Respostas !
http://br.answers.yahoo.com/
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-09-12 20:54 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-12 15:46 Sorting items in aggregate function Steven Murdoch <psql+Steven.Murdoch@cl.cam.ac.uk>
2006-09-12 18:17 ` Michael Fuhr <mike@fuhr.org>
2006-09-12 20:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-12 18:24 ` Volkan YAZICI <yazicivo@ttnet.net.tr>
2006-09-12 20:54 ` Osvaldo Rosario Kussama <osvaldo_kussama@yahoo.com.br>
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