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 2012 Forums
 Transact-SQL (2012)
 DELETE RECORDS ON LESS THAN TWO EXIST

Author  Topic 

jbbatts77
Starting Member

2 Posts

Posted - 2013-03-08 : 14:57:56
Hello,

I have a table that has numerical data in it like so:

3
3
3
3
5
5
5
5
6
6
7
7
7
7
8
9
9
9

How do I delete all records where only 2 or less instances exist? In the above case it would be the two 6's and the 8

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-03-08 : 19:33:13
[CODE]delete MyTable
where MyColumn in (
select MyColumn
from MyTable
group by MyColumn
having count(*) <= 2
)[/CODE]

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-09 : 00:58:37
[code]
delete t
from
(
select cout(1) over (partition by yourcol) AS cnt
from table
)t
where cnt<=2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -