Reputation: 439
Is this something which is possible in MS Access or SQL Server? I tried it in different ways using GROUP BY
clause.
I want to find out the duplicates (concatenating Sales Order and Item) with the sequential number. Column 'schedule' is what I am looking for.
Upvotes: 2
Views: 5489
Reputation: 981
Try this sample data .You will get exact answer you need..
Begin Tran
create table sales
(
salesorder varchar(15),
item varchar(5)
)
insert into sales values('3040117007',1)
insert into sales values('3040117007',1)
insert into sales values('3040117007',1)
insert into sales values('3040179299',1)
insert into sales values('3040179299',1)
insert into sales values('3040182496',1)
insert into sales values('3040182496',1)
insert into sales values('3040182496',1)
insert into sales values('3040182496',1)
select * from sales
;With CTE(Schedule,salesorder,item,concats)
as
(
SELECT ROW_NUMBER() OVER(PARTITION by salesorder,item ORDER BY salesorder )
AS duplicates,salesorder,item,salesorder+''+item
FROM sales
)
--Now get Duplicate Records
select 'Duplicate_Records'* FROM CTE
WHERE Schedule > 1
rollback tran
Replace this query==> "select 'Duplicate_Records'* FROM CTE WHERE Schedule > 1"
by this one "select 'All_Records'* FROM CTE" to get all records
Upvotes: 0
Reputation: 981
Use SQL CTE expression to get concat Duplicate values
Let us keep ur table name as sales and columnname as Salesorder,item,concat
With CTE(Duplicates,item,concat)
as
(
SELECT ROW_NUMBER() OVER(PARTITION by salesorder,item ORDER BY salesorder )
AS duplicates,item, salesorder+''+item
FROM sales
)
--Now Delete Duplicate Records
select * FROM CTE
WHERE duplicates > 1
Try this it will work.
Upvotes: 2