Advanced SELECT

Kai Hessing wrote:

The normal clause would look like:

SELECT c.companyname, s.sectorname FROM company c, sector s,
company_sector cs WHERE cs.cid = c.cid AND cs.sid = s.sid ORDER BY
c.companyname;

c.companyname | ??? (sectors)
---------------+------------------------
company1 | sectora, sectorb
company2 | sectora, sectorb, sectorc
company3 | sectora
company4 | sectorc

Search the mailing-list archives for "custom aggregate concat" and
you'll quickly find an example of how to write your own custom aggregate
(like SUM()).

Warning - I don't think you can guarantee the order of elements in the
aggregated sectors.

--
Richard Huxton
Archonet Ltd

Richard Huxton schrieb:

Search the mailing-list archives for "custom aggregate concat" and
you'll quickly find an example of how to write your own custom aggregate
(like SUM()).

Warning - I don't think you can guarantee the order of elements in the
aggregated sectors.

Thank you very much. This was the right hint where to search. For the
order I'll try to find some possibilities to write a function, that does
a sort on this ;) We'll see...

--
GnuPG-PublicKey -> http://www.hobsons.de/pgp/kai_hessing.asc

Wer Unrecht tut ist ungluecklicher als wer unrecht leidet.
(Demokrit, um 460 v. Chr.)

Richard Huxton <dev@archonet.com> writes:

Search the mailing-list archives for "custom aggregate concat" and
you'll quickly find an example of how to write your own custom aggregate
(like SUM()).

Warning - I don't think you can guarantee the order of elements in the
aggregated sectors.

In recent PG versions you can. For example,

select key1, custom_aggregate(detail_field) from
(select key1, detail_field from my_table order by key1, key2) ss
group by key1 order by key1;

The detail_field values will be fed to the aggregate in order by key2
within each key1 group. This is one of the examples that motivated
allowing ORDER BY in subselects, even though it's outside the SQL spec.

[ experiments... ] This works reliably in 7.4 and up. Before that,
the optimizer didn't make the connection between the sort ordering of
the inner query and that needed by the outer, so it would repeat the
sort step using only key1 and very possibly destroy the key2 ordering.

regards, tom lane

______________________________________________
Renovamos el Correo Yahoo!: �250 MB GRATIS!
Nuevos servicios, m�s seguridad
http://correo.yahoo.es

Hi,

Tom Lane schrieb:

[ experiments... ] This works reliably in 7.4 and up. Before that,
the optimizer didn't make the connection between the sort ordering of
the inner query and that needed by the outer, so it would repeat the
sort step using only key1 and very possibly destroy the key2 ordering.

*Thanks* this was the same idea, that I had. Combined with a DISTINCT
there are no double entries. It works here perfect with 7.39. If someone
is interested, this is the full SELECT:

-----
SELECT case when lower(substring(f.marketingid,1,1))='d' then 'Deutschland'
when lower(substring(f.marketingid,1,1))='s' then 'Schweiz'
else '' end AS bereich,
f.fid AS fid,
f.marketingid AS marketingid,
f.firmalang AS unternehmen,
case when fp.www IS NULL then '' else fp.www end AS url,
case when fp.jobwww IS NULL then '' else fp.jobwww end AS joburl,
case when b.branchen IS NULL then '' else b.branchen end AS
branchen,
case when j.jobtypen IS NULL then '' else j.jobtypen end AS
gesuchte_jobtypen,
case when g.funktionen IS NULL then '' else g.funktionen end AS
gesuchte_funktionen,
case when a.faecher IS NULL then '' else a.faecher end AS
gesuchte_fachrichtungen,
case when s.zusatz IS NULL then '' else s.zusatz end AS
adresse_zusatz,
case when s.strasse IS NULL then '' else s.strasse end AS strasse,
case when s.plz IS NULL then '' else s.plz end AS plz,
case when s.ort IS NULL then '' else s.ort end AS ort,
case when s.land IS NULL then '' else s.land end AS land,
case when m.ansprechpartner IS NULL then '' else
m.ansprechpartner end AS ansprechpartner
FROM firma f
JOIN (
SELECT js.fid, group_concat(js.jobtyp) AS jobtypen FROM (
SELECT DISTINCT f.fid, jt.jobtyp
FROM firma f LEFT OUTER JOIN (jobtext j JOIN jobtyp jt ON
(j.jobtypid=jt.jobtypid)) ON (f.fid=j.fid)
WHERE (j.status>-1 OR j.status IS NULL) AND (jt.status>-1 OR
jt.status IS NULL) AND f.status>-1 ORDER BY jobtyp
) js GROUP BY js.fid
) j ON (f.fid=j.fid)
JOIN (
SELECT bs.fid, group_concat(bs.fbranche) AS branchen FROM (
SELECT DISTINCT f.fid, b.fbranche
FROM firma f LEFT OUTER JOIN (firma_fbranche fb JOIN fbranche b ON
(fb.fbrancheid=b.fbrancheid)) ON (f.fid=fb.fid)
WHERE (b.status>-1 OR b.status IS NULL) AND f.status>-1 ORDER BY fbranche
) bs GROUP BY bs.fid
) b ON (f.fid=b.fid)
JOIN (
SELECT gs.fid, group_concat(gs.bfunktion) AS funktionen FROM (
SELECT DISTINCT f.fid, b.bfunktion
FROM firma f LEFT OUTER JOIN (firma_bfunktion bf JOIN bfunktion b ON
(bf.bfunktionid=b.bfunktionid)) ON (f.fid=bf.fid)
WHERE (b.status>-1 OR b.status IS NULL) AND f.status>-1 ORDER BY
b.bfunktion
) gs GROUP BY gs.fid
) g ON (f.fid=g.fid)
JOIN (
SELECT au.fid, group_concat(au.fach) AS faecher FROM (
SELECT DISTINCT f.fid, a.fach
FROM firma f LEFT OUTER JOIN (firma_fach fa JOIN fach a ON
(fa.fachid=a.fachid)) ON (f.fid=fa.fid)
WHERE (a.status>-1 OR a.status IS NULL) AND f.status>-1 ORDER BY a.fach
) au GROUP BY au.fid
) a ON (f.fid=a.fid)
JOIN (
SELECT ms.fid, group_concat(ms.ansprechpartner) AS ansprechpartner FROM (
SELECT DISTINCT f.fid,
CASE WHEN (m.titel IS NULL OR length(m.titel)<2) THEN '' ELSE
m.titel || ' ' END
|| CASE WHEN m.vname IS NULL THEN '' ELSE m.vname || ' ' END
|| CASE WHEN m.nname IS NULL THEN '' ELSE m.nname END AS
ansprechpartner
FROM firma f LEFT OUTER JOIN ( sitz s JOIN (SELECT * FROM mitarb
WHERE ffunktionid=1 AND status>-1) m ON (s.sitzid=m.sitzid)) ON
(f.fid=s.fid)
WHERE (s.status>-1 OR s.status IS NULL) AND f.status>-1 ORDER BY
ansprechpartner
) AS ms GROUP BY ms.fid
) m ON (f.fid=m.fid)
LEFT OUTER JOIN (
SELECT f.fid, group_concat(s.zusatz) AS zusatz,
group_concat(s.strasse) AS strasse, group_concat(s.plz) AS plz,
group_concat(s.ort) AS ort, group_concat(l.land) AS land
FROM firma f JOIN sitz s ON (f.fid=s.fid) JOIN land l ON
s.landid=l.landid
WHERE s.status>-1 AND s.hauptsitz=1 AND f.status>-1 GROUP BY f.fid
) s ON (f.fid=s.fid)
LEFT OUTER JOIN fplus fp ON (f.fid=fp.fid)
ORDER BY lower(substring(f.marketingid,1,1)), lower(f.firmalang);
------

*greets*
Kai

--
GnuPG-PublicKey -> http://www.hobsons.de/pgp/kai_hessing.asc

Gut ist nicht Nichtfreveln, sondern nicht einmal freveln wollen.
(Demokrit, um 460 v. Chr.)