SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to Delete duplicates values from table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

teamjai
Starting Member

India
44 Posts

Posted - 08/13/2013 :  01:59:48  Show Profile  Reply with Quote
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

Sweden
30240 Posts

Posted - 08/13/2013 :  02:22:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30240 Posts

Posted - 08/13/2013 :  02:23:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

teamjai
Starting Member

India
44 Posts

Posted - 08/13/2013 :  02:42:35  Show Profile  Reply with Quote
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..

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30240 Posts

Posted - 08/13/2013 :  02:45:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote

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
Go to Top of Page

teamjai
Starting Member

India
44 Posts

Posted - 08/13/2013 :  04:18:23  Show Profile  Reply with Quote
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.
Go to Top of Page

teamjai
Starting Member

India
44 Posts

Posted - 08/13/2013 :  07:50:08  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2208 Posts

Posted - 08/13/2013 :  08:00:45  Show Profile  Reply with Quote
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
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 08/13/2013 :  10:52:11  Show Profile  Reply with Quote
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

Go to Top of Page

teamjai
Starting Member

India
44 Posts

Posted - 08/14/2013 :  00:19:28  Show Profile  Reply with Quote
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.
Go to Top of Page

teamjai
Starting Member

India
44 Posts

Posted - 08/14/2013 :  04:19:03  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30240 Posts

Posted - 08/14/2013 :  06:48:19  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

teamjai
Starting Member

India
44 Posts

Posted - 08/14/2013 :  07:45:58  Show Profile  Reply with Quote
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..


Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30240 Posts

Posted - 08/14/2013 :  07:58:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30240 Posts

Posted - 08/14/2013 :  08:01:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000