Actually I now realise that im solving the wrong proplem.
here is a simplified version of my dilema
*****
NAME EMAIL AGE
tom tom@mail 23
dick tom@mail 76
pete pete@email 54
dave cool@mail 21
steve steve@mail 17
mary cool@mail 89
thomas tom@mail 13
rich rich@mail 65
rich dick@mail 33
tom tom@mail 23
tom tom@mail 23
so what i want to do is cut it down so there are no duplicate email
addresses. I want the table looking like this:
*****
NAME EMAIL AGE
tom tom@mail 23
pete pete@email 54
dave cool@mail 21
steve steve@mail 17
rich rich@mail 65
rich dick@mail 33
Cheers tom
-----Original Message-----
From: Hannes Niedner [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 26, 2001 5:45 PM
To: tom harrow; [EMAIL PROTECTED]
Subject: Re: DISTINCT
Hi Tom,
The solution to your problem could be simple if the redundancy is across all
fields. Then you could simply issue a
CREATE table distinct_records
SELECT distinct field_1,.....field_last
FROM table_duplicate_records
WHERE 1=1;
Or
CREATE table distinct_records
SELECT field_1,.....field_last
FROM table_duplicate_records
GROUP BY field_1,.....field_last;
The latter will not work properly if one or more of the fields selected is
not identical for identical combinations of the remaining fields.
Otherwise if you have one or more fields that is not identical among a group
of otherwise (apart from those fields) duplicate records) you must decide if
this difference matters to you or not. If they don¹t matter just select all
where the "duplicates" are identical. Make sure that the fields in the
select and group by part are the same.
Example
table_duplicate records
field_1 field_2 field_3
a b c
a b c
a b d
f g h
f g h
d j k
k i o
CREATE table distinct_records
SELECT field_1, field_2, field_3
FROM table_duplicate_records
GROUP BY field_1, field_2, field_3;
table_distinct_records
field_1 field_2 field_3
a b c
a b d
f g h
d j k
k i o
Got the idea?
Hope that helps
Hannes
On 6/26/01 4:00 AM, "tom harrow" <[EMAIL PROTECTED]> wrote:
> Hi Hannes
>
> I saw your reply to a question someone had regarding the DISTINCT keyword
> and doing the opposite. I too have the same problem... basically need to
> know the values that arnt distinct and get rid of them but keeping one of
> course... so there are no duplicates.
>
> i am trying to write an asp applicatiojn to do it at the mo but its
getting
> quitre complex.
>
> Anyway I thinkk im looking far to deep into the problem and there is
> probably a much simpler way of doing it. any ideas
>
> cheers
>
> Tom Harrow
> Web Developer
>
> Netpoll Ltd
> 9-12 Long Lane
> London EC1A 9HA
> TEL 020 7710 2800
> FAX 020 7710 2828
>
>
>
TEL 020 7710 2800
www.netpoll.net
Netpoll Ltd
9-12 Long Lane
London
EC1A 9HA
This message contains confidential information and is intended only for the individual
or entity named.
If you are not the named addressee you should not disseminate, distrribute or copy
this email.
Please notify the sender or [EMAIL PROTECTED] immediatly by email if you have
received this email by mistake
and delete this email from your system.
Email transmission cannot be guaranteed to be secure or error free as information
could be intercepted, corrupt, lost, destroyed, arrive late
or incomplete and may contain viruses.
The sender therefor does not accept liability for any errors or omissions in the
contents of this message which arrise as a result of
email transmission.
If verification is required please request a hard copy version.
This message has been scanned for viruses with Trend Micro's Virus Wall
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
