delete duplicate records based on unique keys

Question

Post reply

delete duplicate records based on unique keys

gaurav-404321

SSCommitted

Points: 1593
More actions
July 31, 2009 at 12:38 am

#212762

idreg noleavetypeidallowedremarks
19061080072012Night stay with local guardian is not
19091080072012Night stay with local guardian is not
19101080072023Permitted by father on 14/07/09
19071080072023Permitted by father on 14/07/09
19081080072032Permitted by father on 14/07/09
19111080072032Permitted by father on 14/07/09
i want to delete the duplicate records based on regno and leavetypeid. Output should be like this:
idreg noleavetypeidallowedremarks
19091080072012Night stay with local guardian is not
19101080072023Permitted by father on 14/07/09
19111080072032Permitted by father on 14/07/09
from each type of leave id we need to retain max leaveid and delete other one..
there are lot of records in the table for each regsitratrion no. need to apply this soluton for every regsitration number

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

arun.sas SSChampion Points: 11831 More actions · Answer 1

Hi,

try this

create table #temp1

(

id int,

reg_no int,

leave int,

typeid int,

allowed varchar(50),

remarks varchar(50)

)

insert into #temp1

select 1906, 10800720, 1, 2,'Night stay', 'with local guardian is not'

union all

select 1909, 10800720, 1, 2,'Night stay', 'with local guardian is not'

union all

select 1910, 10800720, 2, 3,'Permitted', 'by father on 14/07/09'

union all

select 1907, 10800720, 2, 3,'Permitted', 'by father on 14/07/09'

union all

select 1908, 10800720, 3, 2,'Permitted', 'by father on 14/07/09'

union all

select 1911, 10800720, 3, 2,'Permitted', 'by father on 14/07/09'

01)

select max(id),reg_no,leave,typeid,allowed,remarks into /*temp table*/

from #temp1

group by reg_no,leave,typeid,allowed,remarks

insert this values into temp table and delete the records from the main table then again

insert this temp table recorde to the main table

02)

Delete a

from #temp1 a,

(select min(id)id,reg_no,leave,typeid from #temp1

group by reg_no,leave,typeid

having count(reg_no)> 1)as b

where

a.id = b.id and

a.reg_no = b.reg_no and

a.leave = b.leave and

a.typeid = b.typeid

shamassaeedmr SSC-Addicted Points: 441 More actions · Answer 2

try this one

i want to delete the duplicate records based on regno and leavetypeid. Output should be like this:

Delete from

(

Select rank() over (partition by regno,leavetypid order by regno) as rankid,

[all other columns]

from

table

)

table

where

rankid > 1

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Try this. (using the temp table as proposed by arun.sas) for this test

;with numbered as(SELECT rowno=row_number() over(partition by reg_no,leave,typeid order by reg_no),

reg_no,ID,leave,typeid,allowed,remarks from #temp1)

Then use the following to verify the result.

select * from numbered

If the result is satisfactory then do the work by:

DELETE FROM numbered WHERE Rowno > 1

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

shamassaeedmr (7/31/2009)
try this one
i want to delete the duplicate records based on regno and leavetypeid. Output should be like this:
Delete from
(
Select rank() over (partition by regno,leavetypid order by regno) as rankid,
[all other columns]
from
table
)
table
where
rankid > 1

You really need to try your own suggestion... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

I believe this is what shamassaeedmr was after... it doesn't require a temp table, it leaves only the required rows, and it's nasty fast...

--===== As the others have done, create and populate a test table.
     -- I added the expected clustered primary key.
drop table Temp1
create table Temp1
(
id int PRIMARY KEY CLUSTERED,
reg_no int,
leave int,
typeid int,
allowed varchar(50),
remarks varchar(50)
)

insert into Temp1
select 1906, 10800720, 1, 2,'Night stay', 'with local guardian is not'
union all 
select 1909, 10800720, 1, 2,'Night stay', 'with local guardian is not'
union all 
select 1910, 10800720, 2, 3,'Permitted', 'by father on 14/07/09'
union all 
select 1907, 10800720, 2, 3,'Permitted', 'by father on 14/07/09'
union all 
select 1908, 10800720, 3, 2,'Permitted', 'by father on 14/07/09'
union all 
select 1911, 10800720, 3, 2,'Permitted', 'by father on 14/07/09'

--===== Do the deletes without a temp table or aggregation
;WITH
cteRowNum AS
(
 SELECT ROW_NUMBER() OVER (PARTITION BY Reg_No,Leave,TypeID ORDER BY ID DESC) AS RowNum,
        ID
   FROM Temp1
)
 DELETE cteRowNum
  WHERE RowNum >1

--===== Display the results for confirmation
 SELECT * FROM Temp1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions