zimmer9
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 ?
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)
ASKER
the query returns 2,091 records
************************** ********** ********** ********** ****
then when I exeute:
select distinct account from dbo.tblPWMMatchTest
the result set contains 2,088 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
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
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.
Server: Msg 195, Level 15, State 10, Line 38
'dense_rank' is not a recognized function name.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.