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| 11| 1| 31| 2| 32| 1| 12| 2| 22| 5| 12| 5| 2
(7 rows)

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

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

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

For Jonathan Daves-Brody <jdaves@gilanet.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'.