title: Selecting most recent record in large query based on one value in SELECT field
body: <p>I have a large query, well relatively large.  This query constantly produces multiple results on the MATERIAL column even though I have specified 'DISTINCT'.  I thought by specifying 'DISTINCT' and summing the values I needed that it would produce one line per MATERIAL number.  Can anybody help me see what I am doing wrong here.  I realize the last sub query might be redundant at this point - I was testing and this is what was producing the result I was getting.  </p>
  
  <p>Thanks ahead of time. </p>
  
  <pre><code>DECLARE @daynumber as int
  SET     @daynumber = CASE
                    WHEN DATENAME(dw,GETDATE()) = 'Monday'
                    THEN 56
                    WHEN DATENAME(dw,GETDATE()) = 'Tuesday' 
                    THEN 57
                    WHEN DATENAME(dw,GETDATE()) = 'Wednesday' 
                    THEN 58
                    WHEN DATENAME(dw,GETDATE()) = 'Thursday' 
                    THEN 59
                    WHEN DATENAME(dw,GETDATE()) = 'Friday' 
                    THEN 60
                    END;
  
  SELECT          DISTINCT MATERIAL,
                  PLANT_CODE,
                  STOCK_CATEGORY,
                  material_desc AS 'MATERIAL_DESC',
                  division_id AS 'DIVISION_ID',
                  UPPER(division_desc) AS 'DIVISION_DESC',
                  UPPER(gender_id) AS 'GENDER_ID',
                  UPPER(gender_desc) AS 'GENDER_DESC',
                  DISTRIBUTION_VERSION_CODE,
                  PERIOD_CODE,
                  REQUIREMENTS_DATE,
                  VERSION_IND_FLAG,
                  TECHNICAL_INDEX,
                  SIZE_LITERAL,
                  [ORIGINAL FCST QTY],
                  WITHDRAWN_QUANTITY,
                  [REM PLAN QTY],
                  FUTURE_FCST,
                  TOTAL_OH_INV
  FROM (
  
  SELECT          P1.PLANT_CODE,
                  P1.STOCK_CATEGORY,
                  P1.MATERIAL,
                  M1.material_desc AS 'MATERIAL_DESC',
                  M1.division_id AS 'DIVISION_ID',
                  UPPER(M1.division_desc) AS 'DIVISION_DESC',
                  UPPER(M1.gender_id) AS 'GENDER_ID',
                  UPPER(M1.gender_desc) AS 'GENDER_DESC',
                  P1.DISTRIBUTION_VERSION_CODE,
                  P1.PERIOD_CODE,
                  P1.REQUIREMENTS_DATE,
                  P1.VERSION_IND_FLAG,
                  P1.TECHNICAL_INDEX,
                  P1.SIZE_LITERAL,
                  P1.[ORIGINAL FCST QTY],
                  P1.WITHDRAWN_QUANTITY,
                  P1.[REM PLAN QTY],
                  P2.FUTURE_FCST,
                  SUM(I1.ON_HAND_QUANTITY) AS 'TOTAL_OH_INV'
  FROM
  
  (SELECT         PLANT_CODE,
                  STOCK_CATEGORY,
                  MATERIAL,
                  DISTRIBUTION_VERSION_CODE,
                  PERIOD_CODE,
                  REQUIREMENTS_DATE,
                  PLANNED_QTY AS 'REM PLAN QTY',
                  VERSION_IND_FLAG,
                  SIZE_LITERAL,
                  WITHDRAWN_QUANTITY,
                  TECHNICAL_INDEX,
                  PLANNED_QTY + WITHDRAWN_QUANTITY AS 'ORIGINAL FCST QTY'
   FROM           VW_PLANNED_REQMNTS_TXT 
   WHERE          PLANT_CODE IN ('6040','6041')
   AND            STOCK_CATEGORY IN ('A60385000','A60385003')
   AND            DISTRIBUTION_VERSION_CODE IN ('00','01','ZU','Z2')
   AND            REQUIREMENTS_DATE &lt; GETDATE() - @daynumber
   AND            PLANNED_QTY &gt; 0) AS P1
  
   LEFT OUTER JOIN      
  
   (SELECT
                  SUM(PLANNED_QTY) AS 'FUTURE_FCST',
                  MATERIAL,
                  TECHNICAL_INDEX
   FROM           VW_PLANNED_REQMNTS_TXT P
   WHERE          REQUIREMENTS_DATE &gt;= GETDATE()
   AND            PLANNED_QTY &gt; 0
   AND            STOCK_CATEGORY IN ('A60385000','A60385003')
   GROUP BY       MATERIAL,
                  TECHNICAL_INDEX,
                  SIZE_LITERAL) AS P2
   ON             P1.MATERIAL = P2.MATERIAL
   AND            P1.TECHNICAL_INDEX = P2.TECHNICAL_INDEX
  
   LEFT OUTER JOIN 
  
   (SELECT        ON_HAND_QUANTITY,
                  TECHNICAL_INDEX,
                  MATERIAL,
                  STOCK_CATEGORY,
                  PLANT_CODE
    FROM          VW_INVENTORY I
    WHERE         STOCK_CATEGORY IN ('A60385000','A60385003')
    GROUP BY      TECHNICAL_INDEX,
                  MATERIAL,
                  STOCK_CATEGORY,
                  ON_HAND_QUANTITY,
                  PLANT_CODE) AS I1
    ON            P1.MATERIAL = I1.MATERIAL
    AND           P1.TECHNICAL_INDEX = I1.TECHNICAL_INDEX
    AND           P1.PLANT_CODE = I1.PLANT_CODE
  
    LEFT OUTER JOIN
  
   (SELECT        M.material_number, 
                  M.material_desc,
                  M.division_id,
                  D.division_desc,
                  M.gender_id,
                  G.gender_desc
  FROM            MaterialMaster.dbo.Material M
  
  JOIN            MaterialMaster.dbo.Ref_Division D
  ON              M.division_id = D.division_id
  JOIN            MaterialMaster.dbo.Ref_Gender G
  ON              M.gender_id = G.gender_id) AS M1
  
  ON              P1.MATERIAL = M1.material_number
  
  
  GROUP BY        P1.PLANT_CODE,
                  P1.STOCK_CATEGORY,
                  P1.MATERIAL,
                  P1.DISTRIBUTION_VERSION_CODE,
                  P1.PERIOD_CODE,
                  P1.REQUIREMENTS_DATE,
                  P1.[REM PLAN QTY],
                  P1.VERSION_IND_FLAG,
                  P1.WITHDRAWN_QUANTITY,
                  P1.SIZE_LITERAL,
                  P1.TECHNICAL_INDEX,
                  P1.[ORIGINAL FCST QTY],
                  P2.FUTURE_FCST,
                  M1.material_desc,
                  M1.division_id,
                  M1.division_desc,
                  M1.gender_id,
                  M1.gender_desc) AS T1 /* T1 Represents "Total" for all */
  
  GROUP BY        PLANT_CODE,
                  STOCK_CATEGORY,
                  MATERIAL,
                  material_desc,
                  division_id,
                  division_desc,
                  gender_id,
                  gender_desc,
                  DISTRIBUTION_VERSION_CODE,
                  PERIOD_CODE,
                  REQUIREMENTS_DATE,
                  VERSION_IND_FLAG,
                  TECHNICAL_INDEX,
                  SIZE_LITERAL,
                  [ORIGINAL FCST QTY],
                  WITHDRAWN_QUANTITY,
                  [REM PLAN QTY],
                  FUTURE_FCST,
                  TOTAL_OH_INV
  </code></pre>