CODEWITHSUNDEEP
sqlpartition
Aron.Ridgway
Aron.Ridgway

Reputation: 63

Trying to Show The records with the most recent Date

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'??

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?

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;

Upvotes: 1

Views: 44

Answers (2)

Mureinik
Mureinik

Reputation: 312257

You didn't specify with RDBMS you were using, but most of them only apply aliases after the query is executed. One trick is to wrap the query in an another query that takes care of this condition. E.g.:

SELECT *
FROM   (
    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' ))
)
WHERE rownum = 1;

Upvotes: 2

Bobby
Bobby

Reputation: 2938

Use the MAX aggregate

SELECT plsuppliercontact.plsuppliercontactid, 
       plsupplieraccount.supplieraccountnumber, 
       plsupplieraccount.supplieraccountname, 
       plsupplieraccount.supplieraccountshortname, 
       plsuppliercontactvalue.contactvalue, 
       syscontacttype.name, 
       MAX(plsuppliercontactvalue.datetimecreated)
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' )) 
GROUP BY plsuppliercontact.plsuppliercontactid, 
         plsupplieraccount.supplieraccountnumber, 
         plsupplieraccount.supplieraccountname, 
         plsupplieraccount.supplieraccountshortname, 
         plsuppliercontactvalue.contactvalue, 
         syscontacttype.name

Upvotes: 0

Related Questions