Link to home
Start Free TrialLog in
Avatar of zimmer9
zimmer9Flag for United States of America

asked on

How to extract UNIQUE Account records with the maximum UDL_Date using an SQL statement ?

I am developing an Access application using Access as the front end and SQL Server as the back end database.

I have a table with the following fields:

Field Name                                                  type
-------------                                                  ------------------
Account                                                      nvarchar 255
UDL_Date                                                    datetime  8
ScanDate                                                    datetime  8
Mailroom_Address1                                    nvarchar 255
Mailroom_Address2                                    nvarchar 255
Mailroom_Address3                                    nvarchar 255
Mailroom_Address4                                    nvarchar 255
Mailroom_Address5                                    nvarchar 255

I want to create a SQL statement to return all fields for records with unique Accounts in which the UDL_Date is the most recent date.

For ex:   BEFORE

Account    UDL_Date   ScanDate   M_Add1  M_Add2  M_Add3  M_Add4  M_Add5
12345       9/30/2008    6/30/2008  ABC St    CDE St     EFG St    FGH St    GHI St
12345       1/30/2008    6/30/2008  FRG St    GFR St     KLV St   KRT  St    LOF St
34567       6/30/2008    6/30/2008  FGH St    FTG St     LRF  St   SGE St     LOF  St
34567       8/30/2008    6/30/2008  FTH  St    SGY St    FGT St    FGE St     THG St

           AFTER
-------------------------------------------------------------------------------------------------------    
Account    UDL_Date   ScanDate   M_Add1  M_Add2  M_Add3  M_Add4  M_Add5
12345       9/30/2008    6/30/2008  ABC St    CDE St     EFG St    FGH St    GHI St
34567       8/30/2008    6/30/2008  FTH  St    SGY St    FGT St    FGE St     THG St
   
I used the following SQL code to extract records, but it doesn't return ONLY the unique Accounts. I end up with a few extra Account records.

How would you revise this SQL statement to return ONLY unique Account records for all fields ?


select t1.*
into   dbo.tblPWMMatchTest
from   dbo.tblPWMMatch t1
where  t1.UDL_Date = (select max(t2.UDL_Date) from dbo.tblPWMMatch t2 where  t2.account = t1.account)

Open in new window

Avatar of chapmandew
chapmandew
Flag of United States of America image

select distinct t1.*
into   dbo.tblPWMMatchTest
from   dbo.tblPWMMatch t1
where  t1.UDL_Date = (select max(t2.UDL_Date) from dbo.tblPWMMatch t2 where  t2.account = t1.account)

what does your query return right now? the query is right looking to me.
Avatar of zimmer9

ASKER

the query returns 2,091 records

************************************************************
then when I exeute:      

select distinct account from dbo.tblPWMMatchTest

the result set contains 2,088 records
there are likely some where the date and the account number are exactly the same multipel times, but there are some other fields of data that are different...you know what I mean?  if you're on 2005, you can do this:

select * from (
select ranking = dense_rank() over(partition by accountnumber order by UDL_Date desc, newid()), *
into   dbo.tblPWMMatchTest
from   dbo.tblPWMMatch t1
) a
where ranking = 1
Avatar of zimmer9

ASKER

I'm using Microsoft SQL Server 2000

Server: Msg 195, Level 15, State 10, Line 38
'dense_rank' is not a recognized function name.
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial