title: SQL Select Only the Most Common Results
body: <p>I have a table with IDs and Items where sometimes the associated Item has a variation from the other Items associated with the same ID.  I need a query that selects the most common Item and assigns it to that ID.</p>
  
  <p>The below query works, but I'm hoping to optimize it to avoid having to join two separate CTEs at the end, and rather have one slick SELECT statement:</p>
  
  <pre><code>IF OBJECT_ID('tempdb..#Test') IS NOT NULL
      DROP TABLE #Test
  
  CREATE TABLE #Test
  (
      [ID] INT
      ,[Item] VARCHAR(20)
  )
  
  INSERT #Test
  VALUES
  (100, 'Apple'),
  (100, 'Apple'),
  (100, 'Apples'),
  (200, 'Orange'),
  (200, 'Orange'),
  (200, 'Orange'),
  (200, 'Oranges'),
  (300, 'Grape');
  
  WITH cteOne AS (SELECT
  [ID]
  ,[Item]
  ,COUNT(*) [Count]
  FROM #Test
  GROUP BY [ID]
  ,[Item]
  ),
  cteTwo AS (SELECT
  [ID]
  ,MAX([Count]) [Max]
  FROM cteOne
  GROUP BY [ID])
  
  SELECT
  C1.[ID]
  ,C1.[Item]
  FROM cteOne C1
  INNER JOIN cteTwo C2 ON C2.[ID] = C1.[ID]
  AND C2.[Max] = C1.[Count]
  ORDER BY [ID]
  </code></pre>
  
  <p>Any help is appreciated!</p>