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

To: comp.databases.oracle (N_GRP, Oracle) Subject: Re: Need SQL guru's help: Best query? Date: Tue, 26 Mar 96 22:01:26 GMT
Message-Id: <M.032696.230126.30_at_ping.at> From: whsysana (Hartlieb WILD)
X-Mailer: Quarterdeck Message Center [1.0]

Dear Donna,
I fear that the solution you got replied below (#) will not work; it should read:

SELECT kit_number kit, min(model_number) model FROM moter_table
group by kit_number

-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.

But I can offer another solution to you. You may take this one if you do not want to use the GROUP BY clause, e.g. when you add another column in the SELECT line. My solution uses a subquery finding out the row with the lowest (highest) row-id (that is unique for all rows within a table) for each kit-number (I tested this with tne EMP-table; just change "ename" to "MODEL-NUMBER" and "deptno" to "KIT_NUMBER"

select ename, deptno
from emp
where (rowid, deptno) in
  (select max(rowid), deptno from emp
   group by deptno )

Pleas let me know if my correction and the alternate solution work. Thank you,
sincerely
H. WILD

+==========================================================================+


| Hartlieb  WILD        Systemanalyse    Programmierung     EDV-Training   |

|                     allgemein beeideter gerichtlicher Sachverständiger   |

|                                                                          |

| A-6073 SISTRANS 280   e-mail:  whsysana_at_ping.at     Tel.: (++43) (0)512  |


| Austria               CompuServe:   100131,1552                 378 110  |


+==========================================================================+

---solution (#)---

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.