title: T-SQL Selecting TOP 1 In A Query With Aggregates/Groups
body: <p>I'm still fairly new to SQL. This is a stripped down version of the query I'm trying to run. This query is suppose to find those customers with more than 3 cases and display either the top 1 case or all cases but still show the overall count of cases per customer in each row in addition to all the case numbers. </p>
  
  <p>The TOP 1 subquery approach didn't work but is there another way to get the results I need? Hope that makes sense.</p>
  
  <p>Here's the code:</p>
  
  <pre><code>SELECT t1.StoreID, t1.CustomerID, t2.LastName, t2.FirstName
  ,COUNT(t1.CaseNo) AS CasesCount
  ,(SELECT TOP 1 t1.CaseNo)
  FROM MainDatabase t1
  INNER JOIN CustomerDatabase t2
  ON t1.StoreID = t2.StoreID
  WHERE t1.SubmittedDate &gt;= '01/01/2017' AND t1.SubmittedDate &lt;= '05/31/2017'
  GROUP BY t1.StoreID, t1.CustomerID, t2.LastName, t2.FirstName
  HAVING COUNT (t1.CaseNo) &gt;= 3
  ORDER BY t1.StoreID, t1.PatronID
  </code></pre>
  
  <p>I would like it to look something like this, either one row with just the most recent case and detail or several rows showing all details of each case in addition to the store id, customer id, last name, first name, and case count.</p>
  
  <p><a href="https://i.stack.imgur.com/dOcBl.png" rel="nofollow noreferrer">Data Example</a></p>