Complex query question - 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 2000 Forums

SQL Server Development (2000)

Complex query question

Author

Topic

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2007-01-22 : 11:09:25

ORD MAC
1     fde
2     abc
3     fff
4     fff
5     eee
6     eee

want to delete rows 3 and 5 (keep only one row for each mac with highest ord)

Anyone have an idea how to do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 11:13:38

[code]select mac, max(ord) as ord into #temp from YourTableNameHere group by mac

truncate table YourTableNameHere

insert into YourTableNameHere (ord, mac) select ord, mac from #temp

drop table #temp[/code]
Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-22 : 11:14:49

delete tbl
where ord not in (select max(ord) from tbl group by mac)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 11:18:01

Here is another approach if only a few records are to be deleted

-- prepare sample data
declare	@m table (ord int, mac varchar(20))

insert	@m
select	1, 'fde' union all
select	2, 'abc' union all 
select	3, 'fff' union all
select	4, 'fff' union all
select	5, 'eee' union all
select	6, 'eee'

-- another approach
set rowcount 1

while exists (select top 1 mac from @m group by mac having count(*) > 1)
	delete
	from	@m
	where	mac = (select top 1 mac from @m group by mac having count(*) > 1)

set rowcount 0

select * from @m

Peter Larsson
Helsingborg, Sweden

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-22 : 11:22:10

That would delete a random row not necessarily the earliest ones.

you could though
delete tbl
from tbl t1
where exists (select * from tbl t2 where t1.mac = t2.mac and t1.ord < t2.ord)

and you could add

and t1.mac in (select mac from tbl group by mac having count(*) > 1)

This could turn into a "how many ways can you think of to do this" thread - and I'll add a CTE.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 11:25:42

Another way to go in one step

-- prepare sample data
declare	@m table (ord int, mac varchar(20))

insert	@m
select	1, 'fde' union all
select	2, 'abc' union all
select	3, 'fff' union all
select	4, 'fff' union all
select	5, 'eee' union all
select	6, 'eee'

select * from @m

-- another approach
delete		m
from		@m as m
left join	(
			select		mac,
					max min (ord) as mord
			from		@m
			group by	mac
		) as d on d.mac = m.mac and d.mord = m.ord
where		d.mac is null
	
select * from @m

Peter Larsson
Helsingborg, Sweden

cronincoder
Yak Posting Veteran

56 Posts

Posted - 2007-01-22 : 11:29:19

thanks for the quick responses and solutions! got it working

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-22 : 11:30:22

delete tbl
from
tbl t join
(select mac, ord = max(ord) from tbl group by mac having count(*) > 1) t1
on t.mac = t1.mac
and t.ord <> t1.ord

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-22 : 11:31:17

Is cronincoder trying to stop the fun?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 11:36:47

I think so...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources