need to remove duplicates - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

need to remove duplicates

Author

Topic

ganny
Yak Posting Veteran

51 Posts

Posted - 2010-02-03 : 08:40:32

Hi,

I have a tale with duplicate values. I need to delete them and make the table in order. pls check out the below current table and expected output.

current table:
Code order from to code_use
-----------------------------------------
43000 1 Bang Hyd 43000
- 2 Bang Hyd 43000
- 3 Bang Hyd 43000

21300 1 Chen Hyd 51000
- 2 Chen Hyd 51000
11400 3 Chen Hyd 51000

Output:
Code order from to code_use
-----------------------------------------
43000 1 Bang Hyd 43000
- 2 Chen Hyd 51000

If "code" and "code_use" matches then the record should be there. If "code" and "code_use" not matches then the blank code should be used. All others should be removed.

Please anyone can assist on this.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-03 : 09:05:54

[code]
-- create sample table for testing
declare @sample table
(
code varchar(10),
[order] int,
[from] varchar(10),
[to] varchar(10),
[code_use] varchar(10)
)

-- insert some sample data
insert into @sample
select '43000', 1, 'Bang', 'Hyd', '43000' union all
select '-', 2, 'Bang', 'Hyd', '43000' union all
select '-', 3, 'Bang', 'Hyd', '43000' union all
select '21300', 1, 'Chen', 'Hyd', '51000' union all
select '-', 2, 'Chen', 'Hyd', '51000' union all
select '11400', 3, 'Chen', 'Hyd', '51000'

-- the query
delete d
from (
select *,
row_no = row_number() over (partition by [from], [to], [code_use]
order by case when code <> [code_use] then code end, [order])
from @sample
) d
where d.row_no <> 1

-- the result
select *
from @sample

/*
code order from to code_use
---------- ----------- ---------- ---------- ----------
43000 1 Bang Hyd 43000
- 2 Chen Hyd 51000
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-02-03 : 09:14:00

This will work but its not pretty...someone will be able to optimize better im sure.

Declare @t1 table ( code int, orderID int, StrFrom varchar(20), strTo varchar(20), code_use int)
Insert @t1
Select 43000, 1, 'Bang', 'Hyd', 43000 Union all
Select NULL , 2, 'Bang', 'Hyd', 43000 Union all
Select NULL, 3, 'Bang', 'Hyd', 43000 Union all
Select 21300, 1, 'Chen', 'Hyd', 51000 Union all
Select NULL, 2, 'Chen', 'Hyd', 51000 Union all
Select 11400, 3, 'Chen', 'Hyd', 51000

Select * from @t1;

With cte (code, Orderid, strFrom, strTo, code_use, countOf) 
As( Select * , Count(*) as cte_count from @t1 where code = code_use group by code, Orderid, strFrom, strTo, code_use)

select cte.code, cte.orderID, cte.StrFrom, cte.StrTo, cte.Code_Use from cte

Union All 

Select * from @t1 a where Not exists (select distinct code_use from cte where a.code_use = cte.code_use) and code is null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources