DELETE RECORDS ON LESS THAN TWO EXIST

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

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources