title: SQL query to select the latest records with a distinct subject
body: <p>I am using SQL Server and have a table set up like below:</p>
  
  <pre><code>| id | subject | content | moreContent | modified   |
  | 1  | subj1   | aaaa    | aaaaaaaaaaa | 03/03/2015 |
  | 2  | subj1   | bbbb    | aaaaaaaaaaa | 03/05/2015 |
  | 3  | subj2   | cccc    | aaaaaaaaaaa | 03/03/2015 |
  | 4  | subj1   | dddd    | aaaaaaaaaaa | 03/01/2015 |
  | 5  | subj2   | eeee    | aaaaaaaaaaa | 07/02/2015 |
  </code></pre>
  
  <p>I want to select the latest record for each subject heading, so the records to be returned would be:</p>
  
  <pre><code>| id | subject | content | moreContent | modified   |
  | 2  | subj1   | bbbb    | aaaaaaaaaaa | 03/05/2015 |
  | 3  | subj2   | cccc    | aaaaaaaaaaa | 03/03/2015 |
  
  
  SELECT Subject, MAX(Modified) FROM [CareManagement].[dbo].[Careplans] GROUP BY Subject
  </code></pre>
  
  <p>I could do a query like the one above, but I want to preserve all of the content from the selected rows. To return the content columns I would need to apply an aggregate function, or add them to the group by clause which wouldn't give me the desired effect. 
  I have also looked at nested queries but not found a successful solution yet. If anyone could assist that would be great.</p>