From: | Thom Brown <thom(at)linux(dot)com> |
---|---|
To: | Florian Henge <florian(dot)henge82(at)web(dot)de> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: xml output |
Date: | 2010-08-25 09:05:56 |
Message-ID: | AANLkTimfD3ja3RbgzWeSHLmv7x_jJin-+iaUei+ivnJf@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On 25 August 2010 09:53, Florian Henge <florian(dot)henge82(at)web(dot)de> wrote:
> Hi!
>
> Thanks, that worked perfectly for me, except for one problem.
>
> I have like 20 times the name "Joe" in my table, but want to get it only once in the xml output file.
>
> How can is do this? I tried SELECT DISTINCT but it didn't work.
If you're using PostgreSQL 8.4 or greater you can do:
WITH people AS (
SELECT DISTINCT first_name
FROM person
ORDER BY first_name
)
SELECT
xmlelement(name words,
xmlagg(
xmlforest(first_name)
)
)
FROM people
If not, you can do:
SELECT
xmlelement(name words,
xmlagg(
xmlforest(first_name)
)
)
FROM (
SELECT DISTINCT first_name
FROM person
ORDER BY first_name
) AS people
Although maybe there's a nicer way of doing it. Note that if you want
aggregates in your results, use GROUP BY instead of DISTINCT.
--
Thom Brown
Registered Linux user: #516935
From | Date | Subject | |
---|---|---|---|
Next Message | Siddharth Saha | 2010-08-25 12:11:23 | calling functions which take user defined types |
Previous Message | Florian Henge | 2010-08-25 08:53:04 | Re: xml output |