Try this:
CEATE TABLE new_table
SELECT DISTINCT NAME, EMAIL, AGE
FROM whatever_table
On Wed, 27 Jun 2001, tom harrow wrote:
> 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
> >
> >
> >
>
---------------------------------------------------------------------
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
