On 6/27/01 8:48 AM, "Sherzod Ruzmetov" <[EMAIL PROTECTED]> wrote:
That should work without errors and was one of my suggested ways (can still
be seen down below). The group by query works for me the same way like the
distinct query, given that all selected fields give you a unique combination
(so you could assign a combined primary key from those columns). But you are
certainly right that this is not needed if you don't need the feature to
access the differences created by extra fields among these identical tuples
by using count(), max(), min() and so on.
Cheers Hannes
>
> 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
