title: SQL - Most recent by date
body: <p>I have a consumer table which has the columns - Email, AccountState and DateCreated</p>
  
  <p>AccountState can have the values 1 (active), 2 (inactive) and 3 (archived)</p>
  
  <p>A specific consumer can have multiple rows consisting of the account state's above.</p>
  
  <p>What I am trying to do is construct a query which returns the following </p>
  
  <p>A. A list of consumer records for each consumer (using email address)  </p>
  
  <p>B. Only the records which aren't the most recent (so if a specific email address has 3 records, 1 for each state, it would return the 2 which aren't the most recent)</p>
  
  <p>Then once I have this list I want to set all these states to 3 as they need to be archived.</p>
  
  <p>So for the example data shown here</p>
  
  <p><img src="https://i.stack.imgur.com/s47dx.png" alt="enter image description here"></p>
  
  <p>Only rows 13 - 16 would be returned.</p>
  
  <p>I have tried to do this using the query below but it isn't working.</p>
  
  <pre><code>SELECT                  con.Email,
                          con.Id, 
                          con.DateCreated                 AS DateRegistered,
                          con.DateLastActivity,
                          con.hasiPhone,
                          con.hasAndroid,
                          con.hasSMS,
                          con.CurrencyCode                AS Currency,
                          con.AccountState
  
  
          FROM            Consumer                        con
  
          WHERE           con.AccountState                &lt;&gt; 1
          AND             DateCreated     =(  SELECT  MAX(DateCreated)
                              FROM    Consumer                        con_most_recent
                              WHERE   con_most_recent.AccountState    &lt;&gt; 1
                              AND     con_most_recent.Id              = con.Id)
  
          order by Email asc
  </code></pre>