Re: Problems with distinct

On Monday 13 February 2006 20:22, Tom Lane wrote:
> Andreas Joseph Krogh <andreak(at)officenet(dot)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 = 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 = 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
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
-----+------+------------
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
----------+----------
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 WHERE
child_id = g2.id)
AND g2.id IN(1,2,109,105, 112);
groupname | id | p_id
------------+-----+------
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
list(the IN-clause).

--
Andreas Joseph Krogh <andreak(at)officenet(dot)no>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
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øyen | 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 | |
------------------------+---------------------------------------------+