pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCategories and subcategories : more details
2+ messages / 2 participants
[nested] [flat]
* Categories and subcategories : more details
@ 2001-03-20 16:53 Rachel Coin <rachel@derniere-minute.org>
0 siblings, 1 reply; 2+ messages in thread
From: Rachel Coin @ 2001-03-20 16:53 UTC (permalink / raw)
To: pgsql-sql
> I have two tables : the first for categories and the second for
> subcategories.I want to get a result like Yahoo! For each category ,
> the sql
> query must return maximum 3 subcategories. I can't use "limit" in a
> subquery
> because I use Postgresql 7.0.
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
any help?
Regards,
Rachel
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Categories and subcategories : more details
@ 2001-03-20 17:43 Josh Berkus <josh@agliodbs.com>
parent: Rachel Coin <rachel@derniere-minute.org>
0 siblings, 0 replies; 2+ messages in thread
From: Josh Berkus @ 2001-03-20 17:43 UTC (permalink / raw)
To: Rachel Coin <rachel@derniere-minute.org>; +Cc: pgsql-sql
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@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2001-03-20 17:43 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-20 16:53 Categories and subcategories : more details Rachel Coin <rachel@derniere-minute.org>
2001-03-20 17:43 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox