?
Solved

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

Posted on 2009-04-16
5
Medium Priority
?
255 Views
Last Modified: 2013-12-05
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

0
Comment
Question by:zimmer9
  • 3
  • 2
5 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24160812
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
 

Author Comment

by:zimmer9
ID: 24161133
the query returns 2,091 records

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

select distinct account from dbo.tblPWMMatchTest

the result set contains 2,088 records
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24161178
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
 

Author Comment

by:zimmer9
ID: 24161220
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
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 24161243
:)  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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question