title: How to show all non-aggregate field values
body: <p>I'm probably overlooking something simple, but I don't see it.<br>
  Supposed I have a table of participants and scores</p>
  
  <pre><code>[Name]   [Score]
  Tom      12
  Tom      14
  Tom      20
  Dick     14
  Dick     17
  Harry    23
  </code></pre>
  
  <p>and I want to show everybody who participated more than once, and all their scores. This means that in this example, I want to show all records except for the last one.<br>
  The first part would be easy - just group by name and add <code>having count([Score])&gt;1</code>. But then how to display all the scores too?</p>
  
  <p>I tried experimenting with not grouping and adding <code>count([Score]) over (partition by [Name])</code>, but that's not a valid expression to filter by. You can't do <code>(count([Score]) over (partition by [Name]))&gt;1</code>.</p>
  
  <p>So in the end I went with</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT * from [Table]
  where [Name] in (
       SELECT [Name] from [Table]
       GROUP by [Name]
       HAVING count([Score])&gt;1
  )
  </code></pre>
  
  <p>and this works, but I wonder if there's a better way.<br>
  (The real query is longer and involves more joined tables, which all have to be repeated inside the inner select, so this feels... dirty. Violating the DRY principle.)<br>
  So is this the best I can do? Or is there a better way?</p>