Обсуждение: Problems with distinct

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);

The *only* difference is that the distinct-clause changed place...

--
Andreas Joseph Krogh <andreak@officenet.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 |                                             |
------------------------+---------------------------------------------+

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 = 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.
        regards, tom lane

Andreas Joseph Krogh wrote:

> 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 an SQL keyword, not a function.  The former is exactly equivalent to

SELECT DISTINCT g.groupname, g.id, .... FROM ....

except that the first field is inside a (trivial) expression that makes it look like a function call.

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 = 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 |      | ONPAdmin101 |      | Ansatte102 |  101 | Ledere103 |  101
|IT104 |  101 | Finans105 |  101 | Backoffice106 |  101 | Kunder107 |  102 | Styre108 |  102 | Personal109 |  103 |
Drift110|  103 | Strategi111 |  103 | Software112 |  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 |  101Drift      | 109 |  103Hardware   | 112 |  103IT
  | 103 |  101ONPAdmin   |   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@officenet.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 |                                             |
------------------------+---------------------------------------------+