Re: SQL question, SELECT DISTINCT

[SGreen]

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? Or, is there some other criteria you 
need to consider in order to populate the f1 column of the new table with 
a value from your old table? Or, will the new table provide its own value 
for the f1 column?

If I look at the sample data I set up, I see 3 rows with a unique f2/f3 
combination but you only want to move 1 of them to a new table... Which 
value from f1 do you want to keep and which 2 do you want to "throw away" 
during the move?  You are eliminating f1 values by reducing how many times 
the f2/f3 combination appears in the new table. All I need from you is a 
method to decide which f1 to keep and the SQL writes itself (almost) ;-D

Respectfully,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine



"leegold" <leegold@stripped> wrote on 08/16/2004 12:13:06 PM:

> 
> On Mon, 16 Aug 2004 11:36:32 -0400, SGreen@stripped said:
> > It all depends on which values of f1 you want to ignore.
> > 
> > f1      f2     f3
> > -----   -----  ------
> > val1-1  val2   val3
> > val1-2  val2   val3
> > val1-3  val2   val3
> > 
> > Which value of f1 would you want in your new table? Which ones to 
ignore? 
> 
> I want the DISTINCT to ignore the f1 column completely. But I want all 3
> cols. ie. the entire row selected though. Pretend that f1 is a unique PK
> it'll always be different, I want to DISTINCT to ignore it, so if 2 or
> mores row have dup f2 *and* f3 Distinct will let only one through.
> 
> 
> > Are there other columns (beyond these 3) to move as well?
> 
> Nope. Just 3 cols. Thanks
> 
> > 
> > Yours,
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> > 
> > "leegold" <leegold@stripped> wrote on 08/16/2004 11:29:33 AM:
> > 
> > > say I'm selecting distinct (non-duplicate) rows for insertion,
> > > 
> > > insert into original_table select distinct * from new_table
> > > 
> > > these tables have 3  fields/row. Per the above code all 3 fields are 

> > > evaluated by "distict *" .
> > > 
> > > But my question is: I want to ignore field1, therefore I only want 
to 
> > > test if any rows have field2 *and* field3 as dups - that would be my 

> > > distinct test and then do the insert based on that. Test f2 and f3, 
not
> > > f1.
> > > 
> > > What the sql? Help is greatly appreciated.
> > > 
> > > -- 
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe: http://lists.mysql.com/mysql?unsub=1
> > > 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>