how to write it in most efficient way? - Mailing list pgsql-sql

From hubert depesz lubaczewski
Subject how to write it in most efficient way?
Date
Msg-id 20001109142036.A12281@gruby
Whole thread Raw
List 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 by date:

Previous
From: Forest Wilkinson
Date:
Subject: alter table add column implementation undesirable?
Next
From:
Date:
Subject: shared memory blocks?