Re: Categories and subcategories : more details

Rachel,

See below:

> Examples :
>
> table CATEG :
> ida categ
> 1 X
> 2 Y
> 3 Z
>
> table SUBCATEG :
> idb subcateg ida
> 1 x1 1
> 2 x2 1
> 3 x3 1
> 4 x4 1
> 5 y1 2
> 6 y2 2
> 7 z1 3
>
> I 'd like to obtain a table with maximum 2 subcateg per categ :
>
> table result :
>
> categ subcateg
> X x1
> X x2
> Y y1
> Y y2
> Z z1
>

Without LIMIT in subselects, you're in performance hell as far as doing
the above is concerned. Frankly, I can't see any way to do it that
doesn't involve a PL/pgSQL procedure that generates a temporary table,
and has to run once for every row in your result set. I'll post code
later if nobody comes up with a better solution.

If you knew all of your categories in advance, you could do this via an
annoying UNION statement. I'm assuming, however, that it needs to be
dynamic.

-Josh Berkus

--
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco