March 4, 2014 at 7:16 pm
Hi, I am trying to remove duplicates based on 2 fields (rtno and acctno). Also, I want to leave the first record and remove all other duplicates for [acctno] and [rtno]. Currently I have the below query that will display the unique [acctno] records. I am trying to either edit it or add to it. Also, either a SELECT or DELETE statement will work.
Thanks
SELECT RemitterSemiOutput.date, RemitterSemiOutput.siteid, RemitterSemiOutput.worksrc, RemitterSemiOutput.userbatchno, RemitterSemiOutput.batchno, RemitterSemiOutput.ecid, RemitterSemiOutput.tranno, RemitterSemiOutput.seqno, RemitterSemiOutput.acctno, RemitterSemiOutput.rtno, RemitterSemiOutput.checknbr, RemitterSemiOutput.remittername
FROM RemitterSemiOutput
WHERE (((RemitterSemiOutput.acctno) In (SELECT [acctno]
FROM [RemitterSemiOutput] As Tmp GROUP BY [acctno] HAVING Count(*)>1
)));
March 4, 2014 at 7:23 pm
You can use the following to accomplish what you need:create table RemitterSemiOutput (
rtno int not null
,acctno int not null
)
insert into RemitterSemiOutput
values (1,1)
, (1,1)
, (2,2)
, (3,3)
,(3,4)
,(4,4)
,(4,4)
SELECT *
FROM RemitterSemiOutput;
with cte as
(
select rtno, acctno, row_number() over (partition by rtno, acctno order by rtno) as rn
from RemitterSemiOutput
)
delete from cte where rn > 1;
SELECT *
FROM RemitterSemiOutput
Also, please remember to add your own create table statements along with sample data (like I did for you this time) 🙂
March 4, 2014 at 7:26 pm
I wanted to mention that this is going to be run in Access and the RemitterSemiOutput table is a table populated with data. Would this change any of the code?
Thanks
March 4, 2014 at 8:00 pm
It appears that the cte function is not supported by access sql. Is there any other way I can phrase the code for it to successfully run in access 2007?
Thanks
March 5, 2014 at 7:27 am
You are correct that the cte will not work in Access. Is there anything in the record that makes it unique? i.e. an ID or a date that would be different between the records that have the same rtno and acctno? If there is then you can use something like the following:drop table RemitterSemiOutput;
create table RemitterSemiOutput (
[date] datetime not null
,rtno int not null
,acctno int not null
)
insert into RemitterSemiOutput
values (getdate(), 1,1)
waitfor delay '00:00:00.01'
insert into RemitterSemiOutput
values (getdate(), 1,1)
waitfor delay '00:00:00.01'
insert into RemitterSemiOutput
values (getdate(), 2,2)
waitfor delay '00:00:00.01'
insert into RemitterSemiOutput
values (getdate(), 3,3)
waitfor delay '00:00:00.01'
insert into RemitterSemiOutput
values (getdate(), 3,4)
waitfor delay '00:00:00.01'
insert into RemitterSemiOutput
values (getdate(), 4,4)
waitfor delay '00:00:00.01'
insert into RemitterSemiOutput
values (getdate(), 4,4)
SELECT *
FROM RemitterSemiOutput;
delete from RemitterSemiOutput
FROM RemitterSemiOutput a
join (
SELECT max(RemitterSemiOutput.[date]) as RemitterDate, AcctNo, RtNo
FROM [RemitterSemiOutput]
GROUP BY [acctno], [rtno]
HAVING Count(*)>1
) b on a.[date] = b.RemitterDate
and a.AcctNo = b.AcctNo
and a.RtNo = b.RtNo
SELECT *
FROM RemitterSemiOutput
March 5, 2014 at 8:10 am
Checknbr. Would be unique. I tried running the following code but was getting a "syntax error in from clause" error.
delete from RemitterSemiOutput
FROM RemitterSemiOutput a
join (
SELECT max(RemitterSemiOutput.[checknbr]) as CheckNumber, acctno, rtno
FROM [RemitterSemiOutput]
GROUP BY [acctno], [rtno]
HAVING Count(*)>1
) b on a.checknbr = b. CheckNumber
and a.acctno = b.acctno
and a.rtno = b.rtno
March 5, 2014 at 8:17 am
This might be where my lack of knowledge with Access comes in to play. The code that you have works fine for me (on SQL Server) and I don't see anything that is wrong with it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply