title: SQL Select TOP 1 for each group in subquery
body: <p>Good morning,</p>
  
  <p>I want to alter my query in such a way, that only the top 1, filtered from h.started asc is selected.</p>
  
  <pre><code>select h.started, *  from wshhistory h
  join asset a on h.assetid = a.uid
  inner join 
  (
      select Count(*) as TotalLatest, a.uid, a.deleted from asset a
      join wshhistory h on a.uid = h.assetid
      where h.latest = 1 
      group by a.uid, a.deleted
      having Count(*) &gt; 1
  ) X
  on X.uid = h.assetid
  where X.deleted = 0 and h.latest = 1
  order by h.assetid desc
  </code></pre>
  
  <p>I searched all over, and found in most posts, to use: </p>
  
  <pre><code>ROW_NUMBER() OVER (PARTITION BY a.uid ORDER BY h.started asc) as rn
  </code></pre>
  
  <p>But I can't seem to use this since I need use group by, and this results in the error message:</p>
  
  <blockquote>
    <p>Column 'wshhistory.started' is invalid in the select list because it
    is not contained in either an aggregate function or the GROUP BY
    clause.</p>
  </blockquote>
  
  <hr>
  
  <p>To give some extra info about my query:
  I need to search where I have duplicates of Latest = 1 (table: wshhistory), of the same assetid.  And then I need to set the them all on 0 except the latest one.</p>