title: Trying to Show The records with the most recent Date
body: <p>Im trying to use the Over Partition to create a row number based on SupplierAccountNumber then Sort by DateTimeCreated and then only show record 1. my current script i get an error saying Invalid column name 'RowNum'??</p>
  
  <p>I have a list of email addresses for suppliers which have multiple addresses, i only want to pick out the most recent email address. Is there a better way of doing it?</p>
  
  <pre><code>SELECT plsuppliercontact.plsuppliercontactid, 
         plsupplieraccount.supplieraccountnumber, 
         plsupplieraccount.supplieraccountname, 
         plsupplieraccount.supplieraccountshortname, 
         plsuppliercontactvalue.contactvalue, 
         syscontacttype.name, 
         Rownum = Row_number() 
                    OVER( 
                      partition BY plsupplieraccount.supplieraccountnumber 
                      ORDER BY plsuppliercontactvalue.datetimecreated DESC) 
  FROM   alops.dbo.plsupplieraccount PLSupplierAccount, 
         alops.dbo.plsuppliercontact PLSupplierContact, 
         alops.dbo.plsuppliercontactvalue PLSupplierContactValue, 
         alops.dbo.syscontacttype SYSContactType 
  WHERE  plsupplieraccount.plsupplieraccountid = 
         plsuppliercontact.plsupplieraccountid 
         AND plsuppliercontactvalue.plsuppliercontactid = 
             plsuppliercontact.plsuppliercontactid 
         AND syscontacttype.syscontacttypeid = 
             plsuppliercontactvalue.syscontacttypeid 
         AND (( syscontacttype.name = 'E-mail Address' )) 
         AND rownum = 1;
  </code></pre>