title: BIgquery sql eliminating error record from the below records
body: <p>I'm seeing few below sample duplicate records in more than 200k records</p>
  <pre><code>YEAR|SEASON|DM_NBR|DM_DESC|BYR_NBR|LAST_NM|DEPT_ID|DEPT_DESC|Market Cluster|OFC_NBR|Month|FSCL_MN_ID|Capacity|Total|MINIMUM_CAPACITY|Clear_max|Floor_Capacity|Stock_Capacity|Store_Capacity|Max_Capacity|Space_Max_Capacity
   
  2021|SPRING|1|HOME|8|FURNITURE|23|ARTIST|COLD|1990|OCTOBER|202110|Normal_Capacity|0|0|0|0|0|0|0|0
  2021|SPRING|1|HOME|8|FURNITURE|23|ARTIST|COLD|1990|OCTOBER|202110|Normal_Capacity|490|490|0|0|0|0|0|0
  </code></pre>
  <p>Whereas i am trying to eliminate 1st row in result and retrieve only second row as it is valid record.</p>
  <p>Below is the query which I have tried where as I'm still getting the two rows in the output</p>
  <pre><code>select * from (SELECT * FROM `project_id.backup_dataset.capacity_assortment` where date(lod_tmst)='2021-11-09') a
  INNER JOIN
      (SELECT OFC_NBR,YEAR,DEPT_ID,MONTH,MAX(Total) AS tot_capc,max(MINIMUM_CAPACITY) as min_capc
      FROM `kohls-bda-prd.backup_dataset.capacity_assortment` b where date(b.lod_tmst)='2021-11-09'
      GROUP BY OFC_NBR,YEAR,DEPT_ID,MONTH) b 
  ON a.OFC_NBR = b.OFC_NBR 
  AND a.YEAR= b.YEAR
  AND a.DEPT_ID= b.DEPT_ID
  AND a.MONTH= b.MONTH
  </code></pre>
  <p>Can someone please tell me what is wrong with my query and help me with correct query please.</p>