Query Help - 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 2008 Forums

Transact-SQL (2008)

Query Help

Author

Topic

PeeJay2583
Starting Member

40 Posts

Posted - 2012-07-04 : 08:08:56

I am looking for a query to delete max value from one field where ids are same in other field.

For example table has

ID vALUE
1 23
2 25
2 30
2 50
3 21
4 80

I want to delete a row where id is same and value is max from table.
In this case i want to delete ID 2 AND VALUE 50.

Please help!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-04 : 08:27:34

Try:

delete dt
from
(
select
row_number() over (partition by ID order by VALUE DESC) as rownum, *
from YourTable
)dt
where rownum=1
and exists(select * from YourTable t1 where t1.ID = dt.ID and dt.rownum > 1)

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

PeeJay2583
Starting Member

40 Posts

Posted - 2012-07-04 : 09:04:25

it gives 0 rows affected

result i need is

ID vALUE
1 23
2 25
2 30
3 21
4 80

and delete the max value where id is same
ID Value
2 50

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-04 : 09:17:21

This works:

declare @Sample table (ID int, VALUE int)
insert @Sample
select 1, 23 union all
select 2, 25 union all 
select 2, 30 union all
select 2, 50 union all 
select 3, 21 union all 
select 4, 80 

select * from @Sample

delete dt
from
(
select
row_number() over (partition by ID order by VALUE DESC) as rownum, *
from @Sample
)dt
where rownum=1
and ID in (select ID from @Sample group by ID having count(*) > 1)

select * from @Sample

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

PeeJay2583
Starting Member

40 Posts

Posted - 2012-07-04 : 10:01:07

Worked great!!

Thanks a ton for help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources