title: SQL Select Max(Date) out of rows with Duplicate Id
body: <p>I have a table like the below sample. I'm trying to get the row of every user having status 0 and if there is a duplicate UserId then get the row with  max(CreatedDate) for that user</p>
  <p><strong>TableA</strong></p>
  <pre><code>+----+--------+-------------+--------+
  | Id | UserId | CreatedDate | Status |
  +----+--------+-------------+--------+
  |  1 | a234   | 09/02/2017  |      0 |
  |  2 | a234   | 09/03/2017  |      0 |
  |  3 | a234   | 09/06/2017  |      1 |
  |  4 | a143   | 08/25/2017  |      0 |
  +----+--------+-------------+--------+
  </code></pre>
  <p><strong>Expected Output</strong></p>
  <pre><code>+----+--------+-------------+--------+
  | Id | UserId | CreatedDate | Status |
  +----+--------+-------------+--------+
  |  2 | a234   | 09/03/2017  |      0 |
  |  4 | a143   | 08/25/2017  |      0 |
  +----+--------+-------------+--------+
  </code></pre>
  <p><strong>SQL Query</strong></p>
  <pre><code>SELECT d.Id, d.UserId,d.CreatedDate,d.Status FROM 
  (
      SELECT Id, Max(CreatedDate) as MaxDate  
      FROM TableA
      GROUP BY Id
      HAVING Count(*)&gt;1
  ) r
  INNER JOIN TableA d
  ON d.Id=r.Id AND d.CreatedDate=r.MaxDate AND Status=0
  </code></pre>
  <p>But I think the condition is wrong which should be like a sequence eg:</p>
  <blockquote>
  <p>1st condition Status=0</p>
  <p>2nd Condition If Duplicate UserId exist take Max(CreatedDate) else CreatedDate</p>
  </blockquote>