delete duplicates based on conditions

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

delete duplicates based on conditions

Author

Topic

jawsai1224
Starting Member

3 Posts

Posted - 2009-07-11 : 09:22:02

I have the following table,

+---------------+------+--------+
| gene_id | exp | ipr_id |
+---------------+------+--------+
| EU180575_1.1 | -51 | 235 |
| EU180575_1.1 | -133 | 235 |
| EU180575_1.1 | -47 | 235 |
| EU180575_1.1 | -84 | 235 |
| EU180575_1.1 | -51 | 235 |
| EU180575_1.1 | -5 | 235 |
| EU180575_1.1 | -91 | 235 |
| EU180575_1.1 | -63 | 236 |
| EU180575_1.1 | -147 | 4963 |
| EU180575_10.1 | -27 | 238 |
| EU180575_10.1 | -1 | 238 |
| EU180575_10.1 | -15 | 235 |
+---------------+------+--------+

I want to remove duplicates from ipr_id for each gene_id, in such way that minimum value of that ipr_id is kept and rest has to be deleted. I was not able to use distinct in this case. please guide me

result table looks like

+---------------+------+--------+
| gene_id | exp | ipr_id |
+---------------+------+--------+
| EU180575_1.1 | -133 | 235 |
| EU180575_1.1 | -63 | 236 |
| EU180575_1.1 | -147 | 4963 |
| EU180575_10.1 | -27 | 238 |
| EU180575_10.1 | -15 | 235 |
+---------------+------+--------+

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-11 : 11:00:54

SQL 2000 or 2005/2008 ?

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-11 : 14:13:06

This should work in each version:


declare @yak table (gene_id varchar(50),[exp] int, ipr_id int)
insert @yak
select 'EU180575_1.1',-51,235 union all
select 'EU180575_1.1',-133,235  union all
select 'EU180575_1.1',-47,235 union all
select 'EU180575_1.1',-84,235  union all
select 'EU180575_1.1',-51,235  union all
select 'EU180575_1.1',-5,235  union all
select 'EU180575_1.1',-91,235  union all
select 'EU180575_1.1',-63,236 union all
select 'EU180575_1.1',-147,4963  union all
select 'EU180575_10.1',-27,238  union all
select 'EU180575_10.1',-1,238 union all
select 'EU180575_10.1',-15,235 

select * from @yak

delete y1
from @yak y1
join (select gene_id, min([exp])as [exp],ipr_id from @yak group by gene_id,ipr_id)y2
on y1.gene_id=y2.gene_id and y1.ipr_id=y2.ipr_id and y1.[exp] <> y2.[exp]

select * from @yak

Fred

No, you're never too old to Yak'n'Roll if you're too young to die.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-07-11 : 19:05:29

Declare @yak table (gene_id varchar(50),[exp] int, ipr_id int)
insert @yak
select 'EU180575_1.1',-51,235 union all
select 'EU180575_1.1',-133,235  union all
select 'EU180575_1.1',-47,235 union all
select 'EU180575_1.1',-84,235  union all
select 'EU180575_1.1',-51,235  union all
select 'EU180575_1.1',-5,235  union all
select 'EU180575_1.1',-91,235  union all
select 'EU180575_1.1',-63,236 union all
select 'EU180575_1.1',-147,4963  union all
select 'EU180575_10.1',-27,238  union all
select 'EU180575_10.1',-1,238 union all
select 'EU180575_10.1',-15,235 


Delete Z from (Select *,Row_Number() over (Partition by gene_id,ipr_id order by [exp])as seq
from @yak)Z
Where Z.Seq > 1

select * from @yak

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-12 : 03:30:26

[code]
delete y1
from @yak y1
left join (select gene_id, min([exp])as [exp],ipr_id from @yak group by gene_id,ipr_id)y2
on y1.gene_id=y2.gene_id and y1.ipr_id=y2.ipr_id and y1.[exp] = y2.[exp]
where y2.[exp] is null
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources