Developing Matt

My Technical Journal

Delete With Rank

leave a comment »

If you haven’t played with sql server’s 2005 rank functions then check it out. Two ranking functions that I discovered to be useful for deleting duplicates: rank and row_number. Here’s how I used rank

delete from myTablewhere id in
(
select id from
(
SELECT id,
RANK() OVER (PARTITION BY field1, field2, field3, field4, field5 ORDER BY ID) as SeqNumber
FROM myTable
) as a where seqnumber > 1
)

the msdn books online does a pretty good job of explaining, but here’s a good link as well. Honestly, it’s one of those commands you have to use and experiment with to grasp.

Advertisements

Written by matt

November 28, 2007 at 1:27 pm

Posted in T-Sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: