hi. i have database with two tables like this:
database=> \d groups Table "groups"Attribute | Type | Modifier
-----------+---------+----------------------------------------------id | integer | not null default
nextval('groups_seq'::text)parent_id| integer | not null default 0image_id | integer | not null default 0name |
text | not null default ''
database=> \d g_order Table "g_order"Attribute | Type | Modifier
-----------+---------+-----------------------------------------------id | integer | not null default
nextval('g_order_seq'::text)group_id | integer | not null default 0
data inside are (for test purposes):
database=> select * from groups;id | parent_id | image_id | name
----+-----------+----------+---------------------- 0 | 0 | 0 | 1 | 0 | 0 | RTV 2 |
0 | 0 | AGD 3 | 0 | 0 | MP3 4 | 1 | 0 | Audio 5 | 2 | 0 | Lodwki 6
| 2 | 0 | Kuchenki Mikrofalowe 7 | 4 | 0 | Sony 8 | 4 | 0 | Panasonic
(9 rows)
database=> select * from g_order;id | group_id
----+---------- 1 | 2 2 | 6 3 | 5 4 | 3 5 | 1 6 | 4 7 | 8 8 |
7
(8 rows)
the table g_order allows me to change order of displaying groups without changing
main groups table. just like this:
database=> select g.id, getgrouppath(g.id,'/') from groups g, g_order o where
g.id = o.group_id order by o.id;id | getgrouppath
----+-------------------------- 2 | AGD 6 | AGD/Kuchenki Mikrofalowe 5 | AGD/Lodwki 3 | MP3 1 | RTV 4 | RTV/Audio 8 |
RTV/Audio/Panasonic7 | RTV/Audio/Sony
(8 rows)
o.k. and now comes my problem:
i need to know which group (groups.id) is first (after ordering) subgroup of
group ... for example 4 (rtv/audio).
i'm doing it now with:
SELECT go.group_id
FROM g_order go
WHERE go.id = ( SELECT min(o.id) FROM groups g, g_order o WHERE g.id = o.group_id and
g.parent_id=4and g.id <> 0 )
;
but i feel that there should be a better/faster way to do it.
my tables have primary keys, foreign key (groups.id <=> g_order.group_id),
indices.
any idea how to write a better select to do what i need? or maybe the one i
wrote is the best one?
depesz
--
hubert depesz lubaczewski
------------------------------------------------------------------------ najwspanialszą rzeczą jaką dało nam
nowoczesnespołeczeństwo, jest niesamowita wręcz łatwość unikania kontaktów z nim ...