CODEWITHSUNDEEP
sqlsql-serverms-access
santhosha
santhosha

Reputation: 439

Numbering Duplicates with sequential numbers

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.

duplicates numbering

Upvotes: 2

Views: 5489

Answers (2)

Hell Boy
Hell Boy

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

Hell Boy
Hell Boy

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

Related Questions