Re: Problems with distinct
От | Andreas Joseph Krogh |
---|---|
Тема | Re: Problems with distinct |
Дата | |
Msg-id | 200602132029.21193.andreak@officenet.no обсуждение исходный текст |
Ответ на | Re: Problems with distinct (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-sql |
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 | | ------------------------+---------------------------------------------+
В списке pgsql-sql по дате отправления: