title: How to Select * Where Everything is Distinct Except One Field
body: <p>I'm trying to pull 6 records using the code below but there are some cases where the information is updated and therefore it is pulling duplicate records.</p>
  
  <p>My code:</p>
  
  <pre><code>SELECT column2, count(*) as 'Count'
  FROM ServiceTable p
  join HIERARCHY h
  on p.LOCATION_CODE = h.LOCATION
  where Report_date between '2017-04-01' and '2017-04-30'
  
  
  and Column1 = 'Issue '
  and LOCATION = '8789'
  
  and
  ( record_code = 'INCIDENT' or
      (
      SUBMIT_METHOD = 'Web' and
      not exists
      (
      select *
      from ServiceTable p2
      where p2.record_code = 'INCIDENT'
          and p2.incident_id = p.incident_id      
          )
  
  
      )
  )
  </code></pre>
  
  <p>The problem is that instead of the six records it is pulling eight. I would just use distinct * but the file_date is different on the duplicate entries:</p>
  
  <pre><code>FILE_DATE     Incident_ID       Column1        Column2
   4/4/17          123              Issue      Service - Red
   4/4/17          123              Issue      Service - Blue
   4/5/17          123              Issue      Service - Red
   4/5/17          123              Issue      Service - Blue
  </code></pre>
  
  <p>The desired output is:</p>
  
  <pre><code>  COLUMN2         COUNT
   Service - Red      1
   Service - Blue     1
  </code></pre>
  
  <p>Any help would be greatly appreciated! If you need any other info just let me know.</p>