Re: SQL question, SELECT DISTINCT

[Michael Stassen]

Then I'd suggest you declare f1 as an AUTO_INCREMENT column in the target 
table, leave it out of the SELECT, and let it auto-generate IDs.  Something 
like this:

   INSERT INTO original_table (f2, f3)
   SELECT DISTINCT f2, f3 FROM new_table;

I did that in the same order as your original message, though I'd have 
expected original_table and new_table to be swapped, based on their names.

See the manual <http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html> for 
the details on INSERT...SELECT.

Michael

leegold wrote:

> On Mon, 16 Aug 2004 13:57:13 -0400, "Michael Stassen"
> <Michael.Stassen@stripped> said:
> 
>>You were perfectly clear.  We understand that you only want to test f2
>>and f3 for uniqueness.  The question is, which of the possible values
>>of f1 do you want to get.  Do you see?  For a particular unique f2, f3
>>combination, there may be multiple f1 values.  How should we choose
>>which one to put in the new table?  
> 
> 
> Oh, I understand now, sorry. If I said "it makes no difference" then
> you'd ask what the heck I have f1 for in the first place...It actually
> doesn't make a difference. Maybe I should drop f1. f1 is an
> auto-increment int. so I imagine I'd want f1 re-incremented in numerical
> order to take the gaps out.
> 
> Not exactly normalized (or normal:^), thanks.
> 
> 
> 
> That is what Shawn has asked
> 
>>twice, and you have not answered.  Until you answer that, no one can
>>provide a correct solution.
>>
>>Michael
>>
>>leegold wrote:
>>
>>
>>>On Mon, 16 Aug 2004 12:39:32 -0400, SGreen@stripped said:
>>>
>>>
>>>>Let me see if I can explain it a little better....If you need to
>>>>move all 3 columns to the new table but you only want *1* row where
>>>>f2 and f3 have a unique combination of values, how do you want to
>>>>choose *which* value of f1 to move over with that combination? Do
>>>>you want the minimum value, the maximum value, or no value at all?
>>>
>>>
>>>Whoa, it's not that complicated....I want to test only f2 && f3 for
>>>uniqueness, not f1 && f2 && f3. That's all. If I'm not making
> it
>>>clear - don't worry...it's not life or death. Thanks. ...snip...
>>>
>>
>