Re: [SQL] DISTINCT on multiple fields (fwd)

On Thu, 1 Jul 1999, Matthew Hagerty wrote:

> I need a query that will remove duplicate records if three fields are the
> same, i.e. the name, address, and VIN. I have been messing around with
> DISTINCT and DISTINCT ON but I cannot get the results I'm looking for.
>
You have to get the results you looking for.
On my postgres-6.4.2:

create table qqq (q1 int4, q2 int4, q3 int4);
insert into qqq values (1,1,1);
insert into qqq values (2,1,1);
insert into qqq values (1,1,1);
insert into qqq values (1,1,3);
insert into qqq values (1,2,3);
insert into qqq values (1,2,3);
insert into qqq values (2,2,2);
insert into qqq values (2,5,2);
insert into qqq values (2,5,1);

select distinct q1, q2, q3 from qqq; <== you need something like this

q1|q2|q3
--+--+--
1| 1| 1
1| 1| 3
1| 2| 3
2| 1| 1
2| 2| 2
2| 5| 1
2| 5| 2
(7 rows)

select distinct on q1 q1, q2, q3 from qqq;

q1|q2|q3
--+--+--
1| 1| 1
2| 1| 1
(2 rows)

@------------------+-----------------------------------------------@
| Fomichev Michail | The Government of Kamchatka region. |
|<fomichev(at)null(dot)ru>| The Labour and Social Development Department. |
`------------------+-----------------------------------------------'

For Jonathan Daves-Brody <jdaves(at)gilanet(dot)com> wrote to me:
I can't send mail to answer your question. My mails are bounced. Try again
with another mailbox not on `gilanet.com'.