pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: hubert depesz lubaczewski <depesz@depesz.pl>
To: PGSQL-SQL <pgsql-sql@postgresql.org>
Subject: how to write it in most efficient way?
Date: Thu, 9 Nov 2000 14:20:36 +0100
Message-ID: <20001109142036.A12281@gruby> (raw)

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 ...



Message-ID: <20001109142036.A12281@gruby>
Permalink:  ../20001109142036.A12281@gruby/
Also on:    postgresql.org/message-id/20001109142036.A12281@gruby

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: depesz@depesz.pl
  Subject: Re: how to write it in most efficient way?
  In-Reply-To: <20001109142036.A12281@gruby>
  References: <20001109142036.A12281@gruby>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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