Re: Need SQL guru's help: Best query?

John's answer gives one kit per model and not one model per kit. How bout:

select kit_number, max(model_number)
from motor
group by kit_number;

Is this a trick question?? ;)

jstrange_at_imtn.dsccc.com (John Strange) wrote:
>column model format a5
>SELECT min(kit_number) kit, model_number model
>FROM moter_table
>group by model_number
>/
>
>
>Donna Kray (kray.donna_at_mlink.motors.ge.com) wrote:
>:> I have the table
 

>:> MOTOR_TABLE
>:> ============================
 

>:> MODEL_NUMBER KIT_NUMBER
>:> ------------- ------------
>:> A 7
>:> B 7
>:> C 4
>:> D 5
>:> E 7
>:> F 5
>:> G 5
>:> H 4
>
>
>:> MODEL_NUMBER is the primary key for the table. The kits can be used by
>:> one or more models.
 

>:> I need a query to return distinct KIT_NUMBER values with one (any one)
>:> MODEL_NUMBER. E.g.
 

>:> KIT_NUMBER MODEL_NUMBER
>:> ----------- ------------
>:> 4 H (could be C as well)
>:> 5 D (could be F or G as well)
>:> 7 E (could be A or B as well)
 

>:> I don't care which MODEL_NUMBER is returned with the KIT_NUMBER!! Just so
>:> *every* KIT_NUMBER has an example MODEL_NUMBER.
 

>:> How do I get this result?
 

>:> TIA,
>:> DL Kray
>:> kray.donna_at_mlink.motors.ge.com
>
>
>--
>This posting represents the personal opinions of the author. It is not the
>official opinion or policy of the author's employer. Warranty expired when you
>opened this article and I will not be responsible for its contents or use.
Received on Mon Mar 25 1996 - 00:00:00 CET