remove duplicate rows from result set

• barcoder

  SSCommitted

  Points: 1655

  More actions

  July 22, 2004 at 11:50 am

  #152238

  I am trying to extract data from an external source where I have a Productmaster table and a Factorymaster table. Products can be made at more than one factory.

  simplifying the model

  Productmaster contains 2 fields Productcode char(8) and MadebyFactoryKey (int)

  Factorymaster contains Factorykey (int) and PlantName char(20)

  The sql statement I have is

  SELECT DISTINCT P.Productcode,F.PlantName FROM Productmaster P INNER JOIN Factorymaster F ON P.MadeByFactoryKey=F.FactoryKey

  However this returns several rows for each product.

  The output that I want to achieve is one row for each product and where there are more than one madebyfactory, only show the first value.

  Can anyone offer any advice.

   

  Thanks

   

   

   

• JourneymanProgrammer

  SSCommitted

  Points: 1546

  More actions

  July 22, 2004 at 12:41 pm

  #515748

  Hey barcoder,

  Check out this SQL to see if this is what you are looking for or helps you get there:

  SELECT

    P.Productcode,

    PlantName = MIN(F.PlantName)

  FROM

    Productmaster P INNER JOIN Factorymaster F ON P.MadeByFactoryKey = F.FactoryKey

  GROUP BY

    P.Productcode

  JP