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 0
image_id | integer | not null default 0
name | 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/Panasonic
7 | 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=4 and
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 nowoczesne społeczeństwo,
jest niesamowita wręcz łatwość unikania kontaktów z nim ...
