Sorry I didn't get this uploaded the other night, the kids have been sick.
Suppose you have a table with the following structure:
id - int PrimaryKey
name - varchar(50)
description - varchar(MAX)
and the following values:
1 Joe Short FOR Joseph
2 Dave Short FOR David
3 Joe Short FOR Joseph
4 Joe Short FOR Joseph
5 Chris Short FOR Christian
6 Rob Short FOR Robert
Notice that "Joe - Short for Joseph" has three duplicate records. It is true that we can use SELECT DISTINCT to filter these, and for a simple table like this, that's probably the best option. However, sometimes SELECT DISTINCT gets a little hairy when dealing with joins, at least in my experience. So, without having the time to create a complex data structure, or using one I already have that contains confidential data, let's use this simple example.
MSSQL 2005 added a handy new function called ROW_NUMBER(). Learn it, love it (lol). Seriously though, it will make your life easier. What this function does is allow you to get the row number of a record in a returned data table. On the surface, this doesn't sound like much. But, it becomes extremely useful when you realize that you can partition, or group, the records. Let use this on the table shown above:
WITH names AS
(
SELECT
id,
name,
description,
ROW_NUMBER() OVER(PARTITION BY name, description ORDER BY id) AS rowNum
FROM
table_1
)
SELECT
id,
name,
description,
rowNum
FROM
names
We get these results:
id name description rowNum
5 Chris Short FOR Christian 1
2 Dave Short FOR David 1
1 Joe Short FOR Joseph 1
3 Joe Short FOR Joseph 2
4 Joe Short FOR Joseph 3
6 Rob Short FOR Robert 1
Notice that now we have rowNumbers for each record, and they are partitioned by the name and description fields. So, if we want to get the duplicate records for this table, we add a WHERE clause to the query:
SELECT
id,
name,
description
FROM
names
WHERE
rowNum > 1
which gives us:
3 Joe Short FOR Joseph 2
4 Joe Short FOR Joseph 3
Cool isn't it?
What this allows us to do, is dump this into a temporary table to use as in a subquery...
DECLARE @tblNames TABLE(id INT);
WITH names AS
(
SELECT
id,
name,
description,
ROW_NUMBER() OVER(PARTITION BY name, description ORDER BY id) AS rowNum
FROM
table_1
)
INSERT
@tblNames
SELECT
id
FROM
names
WHERE
rowNum > 1
DELETE
FROM
table_1
WHERE
id IN
(
SELECT
id
FROM
@tblNames
)
Now if we select the records from table_1, the duplicates have been eliminated. Here's the results:
1 Joe Short FOR Joseph
2 Dave Short FOR David
5 Chris Short FOR Christian
6 Rob Short FOR Robert
There's a way to do this without the temp table, but I can't find my original query, so I had to do it from memory.
Sorry to turn this into a novel, but I thought an explanation would be better than a code dump.
Offline 
Unsolved 
