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
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco
