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

zimmer9Asked:
Who is Participating?
 
chapmandewCommented:
:)  You posted in the 2005 zone.

Since you're on 2000 the query I gave you is about the best I can do w/ the information you've given me.  
0
 
chapmandewCommented:
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.
0
 
zimmer9Author Commented:
the query returns 2,091 records

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

select distinct account from dbo.tblPWMMatchTest

the result set contains 2,088 records
0
 
chapmandewCommented:
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
0
 
zimmer9Author Commented:
I'm using Microsoft SQL Server 2000

Server: Msg 195, Level 15, State 10, Line 38
'dense_rank' is not a recognized function name.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.