how to write it in most efficient way?
От | hubert depesz lubaczewski |
---|---|
Тема | how to write it in most efficient way? |
Дата | |
Msg-id | 20001109142036.A12281@gruby обсуждение исходный текст |
Список | pgsql-sql |
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 ...
В списке pgsql-sql по дате отправления: