From: Andreas Joseph Krogh <andreak@officenet.no>
Organization: OfficeNet AS
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Problems with distinct
Date: Mon, 13 Feb 2006 20:29:21 +0100
User-Agent: KMail/1.9.1
Cc: pgsql-sql@postgresql.org
References: <200602132019.30393.andreak@officenet.no>
	<3717.1139858520@sss.pgh.pa.us>
In-Reply-To: <3717.1139858520@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200602132029.21193.andreak@officenet.no>

On Monday 13 February 2006 20:22, Tom Lane wrote:
> Andreas Joseph Krogh <andreak@officenet.no> writes:
> > Any idea why this works:
> > SELECT distinct(g.groupname), g.id, g.p_id FROM onp_group g, onp_group =
g2
> > WHERE g.id IN(SELECT g2.id UNION SELECT group_id FROM onp_group_children
> > WHERE child_id =3D g2.id)
> > AND g2.id IN(1,2,109,105, 112);
> >
> > And not this:
> >
> > SELECT g.id, g.p_id, distinct(g.groupname) FROM onp_group g, onp_group =
g2
> > WHERE g.id IN(SELECT g2.id UNION SELECT group_id FROM onp_group_children
> > WHERE child_id =3D g2.id)
> > AND g2.id IN(1,2,109,105, 112);
>
> DISTINCT is not a function, it's a modifier attached to SELECT.  The
> parentheses in your first example are a no-op.

Thanks!
Is there any better(faster) way to achieve the same results based on these=
=20
schemas:

CREATE TABLE onp_group(
id integer PRIMARY KEY REFERENCES onp_entity(id) on delete cascade,
p_id integer REFERENCES onp_group(id) on delete cascade,
groupname varchar NOT NULL unique
);

CREATE TABLE onp_group_children(
group_id integer NOT NULL REFERENCES onp_group(id),
child_id integer NOT NULL REFERENCES onp_group(id),
UNIQUE(group_id, child_id)
);


select * from onp_group;
 id  | p_id | groupname
=2D----+------+------------
   1 |      | SuperAdmin
   2 |      | ONPAdmin
 101 |      | Ansatte
 102 |  101 | Ledere
 103 |  101 | IT
 104 |  101 | Finans
 105 |  101 | Backoffice
 106 |  101 | Kunder
 107 |  102 | Styre
 108 |  102 | Personal
 109 |  103 | Drift
 110 |  103 | Strategi
 111 |  103 | Software
 112 |  103 | Hardware

select * from onp_group_children;
 group_id | child_id
=2D---------+----------
      101 |      102
      101 |      103
      101 |      104
      101 |      105
      101 |      106
      102 |      107
      101 |      107
      102 |      108
      101 |      108
      103 |      109
      101 |      109
      103 |      110
      101 |      110
      103 |      111
      101 |      111
      103 |      112
      101 |      112


The results I'm looking for is this:
SELECT distinct g.groupname, g.id, g.p_id FROM onp_group g, onp_group g2
WHERE g.id IN(SELECT g2.id UNION SELECT group_id FROM onp_group_children WH=
ERE=20
child_id =3D g2.id)
AND g2.id IN(1,2,109,105, 112);
 groupname  | id  | p_id
=2D-----------+-----+------
 Ansatte    | 101 |
 Backoffice | 105 |  101
 Drift      | 109 |  103
 Hardware   | 112 |  103
 IT         | 103 |  101
 ONPAdmin   |   2 |
 SuperAdmin |   1 |

Which is "give me all groups, including parents, for all "id" in the given=
=20
list(the IN-clause).

=2D-=20
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
=2D-----------------------+---------------------------------------------+
OfficeNet AS            | The most difficult thing in the world is to |
Hoffsveien 17           | know how to do a thing and to watch         |
PO. Box 425 Sk=F8yen      | somebody else doing it wrong, without       |
0213 Oslo               | comment.                                    |
NORWAY                  |                                             |
Phone : +47 22 13 01 00 |                                             |
Direct: +47 22 13 10 03 |                                             |
Mobile: +47 909  56 963 |                                             |
=2D-----------------------+---------------------------------------------+