title: tsql Optimize select for aggregated fields
body: <p>I have a task to  select common addresses who have >= 3 different last names: 123 Main for my example.</p>
  
  <p>I coded this in several steps, just want to learn what would be other solution, I think it can be done somehow in single step, I'm on SQL Server 2016.</p>
  
  <p>This Min/Max will work for 2+ different names, but with 3+ I need do more logic  I think</p>
  
  <pre><code>;WITH c AS 
  (
      SELECT 'Dow' LName, 'John' Fname, '123 Main' addr  
      UNION 
      SELECT 'Alexander' LName, 'Mike' Fname, '123 Main' addr  
      UNION
      SELECT 'Sam' LName, 'Kent' Fname, '123 Main' addr  
      UNION 
      SELECT 'Marth' LName,  'Rose' Fname,'123 Main' addr  
      UNION 
      SELECT 'Xavier' LName, 'Rob' Fname, '9000 Metaxa St' addr  
      UNION 
      SELECT 'Xavier' LName,  'Mary' Fname,'9000 Metaxa St' addr  
      UNION 
      SELECT '__Xavier' LName,  'Lucricia' Fname,'9000 Metaxa St' addr  
      UNION 
      SELECT 'Xclude' LName, 'John' Fname, 'Kirov 2 St' addr 
  )
  , --  SELECT * FROM c
  c2 AS 
  (
      SELECT MIN(lname) nameMin, MAX(lname) nameMax, addr, COUNT(*) cc
      FROM c
      GROUP BY addr
  )
  SELECT * 
  FROM c2 
  WHERE nameMin &lt;&gt; nameMax AND cc &gt;=3 
  </code></pre>