How to Delete duplicates values from table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to Delete duplicates values from table

Author

Topic

teamjai
Yak Posting Veteran

52 Posts

Posted - 2013-08-13 : 01:59:48

Table Design --> Componenet (table name)

ID.
Country ID
BT-ID
Component
Activation

i need to delete the duplicate values from Componenet table

ID CountryID BT-ID Componenet Activation
A3CD GD58 TR77 RX 1
BER2 GD58 TR77 RX 1
XEW7 GD58 TR77 MX 1
O4T4 GD58 TR77 MX 1
PE78 GD58 TR77 GX 1

Expected Output

ID CountryID BT-ID Componenet Activation
A3CD GD58 TR77 RX 1
XEW7 GD58 TR77 MX 1
PE78 GD58 TR77 GX 1

Please help me

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-13 : 02:22:28

DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY CountryID, [BT-ID], Component, Activation) AS rn
FROM dbo.Table1
) AS f
WHERE rn > 1;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-13 : 02:23:13

SELECT ID, CountryID, [BT-ID], Component, Activation
FROM (
SELECT ID, CountryID, [BT-ID], Component, Activation, ROW_NUMBER() OVER (PARTITION BY CountryID, [BT-ID], Component, Activation) AS rn
FROM dbo.Table1
) AS f
WHERE rn = 1;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

teamjai
Yak Posting Veteran

52 Posts

Posted - 2013-08-13 : 02:42:35

quote:
Originally posted by SwePeso

DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY CountryID, [BT-ID], Component, Activation) AS rn
FROM dbo.Table1
) AS f
WHERE rn > 1;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Thank you for your help..

i got below error msg

Msg 4112, Level 15, State 1, Line 11
The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.

please guide me..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-13 : 02:45:31

[code]
DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY CountryID, [BT-ID], Component, Activation ORDER BY CountryID) AS rn
FROM dbo.Table1
) AS f
WHERE rn > 1;
[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

teamjai
Yak Posting Veteran

52 Posts

Posted - 2013-08-13 : 04:18:23

quote:

Originally posted by SwePeso


DELETE  f
FROM    (
                SELECT  ROW_NUMBER() OVER (PARTITION BY CountryID, [BT-ID], Component, Activation ORDER BY CountryID) AS rn
                FROM    dbo.Table1
        ) AS f
WHERE   rn > 1;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Thank you . now its work.

teamjai
Yak Posting Veteran

52 Posts

Posted - 2013-08-13 : 07:50:08

quote:

Originally posted by SwePeso


DELETE  f
FROM    (
                SELECT  ROW_NUMBER() OVER (PARTITION BY CountryID, [BT-ID], Component, Activation ORDER BY CountryID) AS rn
                FROM    dbo.Table1
        ) AS f
WHERE   rn > 1;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

i have one more clarification. i need to check Activation value

if Activation is 0 or is NuLL then delete the rows ,
if Activation is 1 then dont delete that corresponding rows

Please help me.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-13 : 08:00:45

[code]DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY CountryID, [BT-ID], Component, Activation ORDER BY CountryID) AS rn, Activation
FROM dbo.Table1
) AS f
WHERE rn > 1
AND ISNULL(f.Activation, 0) !=0 ;[/code]

--
Chandu

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-13 : 10:52:11

Minor correction:

quote:

Originally posted by bandi

DELETE  f
FROM    (
                SELECT  ROW_NUMBER() OVER (PARTITION BY CountryID, [BT-ID], Component, Activation ORDER BY CountryID) AS rn, Activation
                FROM    dbo.Table1
        ) AS f
WHERE   rn > 1
	AND ISNULL(f.Activation, 0) = 0 ;

--
Chandu

teamjai
Yak Posting Veteran

52 Posts

Posted - 2013-08-14 : 00:19:28

quote:

Originally posted by MuMu88

Minor correction:

quote:

Originally posted by bandi

DELETE  f
FROM    (
                SELECT  ROW_NUMBER() OVER (PARTITION BY CountryID, [BT-ID], Component, Activation ORDER BY CountryID) AS rn, Activation
                FROM    dbo.Table1
        ) AS f
WHERE   rn > 1
	AND ISNULL(f.Activation, 0) = 0 ;

--
Chandu

Thank you.

Now i need to check activation using case condition

Activation 1 and 0 then delete Activation 0 rows
Activation 1 and 1 then delete any rows
Activation 1 and NUll then delete NULL rows
Activation 0 and NULL then delete any rows.

is it possible.. using case condition..

Please help.

teamjai
Yak Posting Veteran

52 Posts

Posted - 2013-08-14 : 04:19:03

quote:
Originally posted by teamjai

quote:
Originally posted by MuMu88

Minor correction:

quote:
Originally posted by bandi

DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY CountryID, [BT-ID], Component, Activation ORDER BY CountryID) AS rn, Activation
FROM dbo.Table1
) AS f
WHERE rn > 1
AND ISNULL(f.Activation, 0) = 0 ;

--
Chandu

Thank you.

Now i need to check activation using case condition

Activation 1 and 0 then delete Activation 0 rows
Activation 1 and 1 then delete any rows
Activation 1 and NUll then delete NULL rows
Activation 0 and NULL then delete any rows.

is it possible.. using case condition..

Please help.

Any suggestion for above issue. Please help me

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-14 : 06:48:19

Post some more samples with ALL expected combinations.
Then post the expected output with ALL the rules above applied and make a comment which rule did what.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

teamjai
Yak Posting Veteran

52 Posts

Posted - 2013-08-14 : 07:45:58

Query:

DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY CountryID, [BT-ID], Component, Activation ORDER BY CountryID) AS rn, Activation
FROM dbo.Table1
) AS f
WHERE rn > 1
AND ISNULL(f.Activation, 0) = 0 ;

The above query rremove the duplicates values from table (dbo.Table1)

Table Values:

ID CountryID BT-ID Componenet Activation
------------------------------------------
A3CD GD58 TR77 RX 1
BER2 GD58 TR77 RX 0
------------------------------------------
XEW7 GD58 TR77 MX 1
O4T4 GD58 TR77 MX 1
-------------------------------------------
PE78 GD58 TR77 GX NuLL
GE44 GD58 TR77 GX 0
---------------------------------------------
PR98 GD58 TR75 TX 0
WE47 GD58 TR75 TX 0
------------------------------------------------

Expected Output

ID CountryID BT-ID Componenet Activation
--------------------------------------------------
A3CD GD58 TR77 RX 1
--------------------------------------------------
O4T4 GD58 TR77 MX 1 or 1 (Delete any one)
------------------------------------------------------
GE44 GD58 TR77 GX 0 or NULL (Delete any one)
--------------------------------------------------------
PR98 GD58 TR75 TX 0 or 0 (Delete any one)
----------------------------------------------

is it possible using a Query ?

Guide me..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-14 : 07:58:50

[code]DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY CountryID, [BT-ID], Component ORDER BY [Activation] DESC) AS rn
FROM dbo.Table1
) AS f
WHERE rn > 1;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-14 : 08:01:15

My above query prioritizes the Activation column. Keep (in order) 1, 0 and NULL.
If there is a row with 1 for Activation, the code will keep that row and omit other 1 rows and 0 rows and NULL rows.
If there are no rows with 1, then the code keep the zero row and omit other zero rows and NULL rows.
If there are no rows with 0, then the code keep the NULL row and omit other NULL rows.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources