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

Posted on 2009-04-16
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

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

Question by:zimmer9
    LVL 60

    Expert Comment

    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.

    Author Comment

    the query returns 2,091 records

    then when I exeute:      

    select distinct account from dbo.tblPWMMatchTest

    the result set contains 2,088 records
    LVL 60

    Expert Comment

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

    Author Comment

    I'm using Microsoft SQL Server 2000

    Server: Msg 195, Level 15, State 10, Line 38
    'dense_rank' is not a recognized function name.
    LVL 60

    Accepted Solution

    :)  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.  

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now