Re: Sorting items in aggregate function

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/