How to group by to get rid of duplicates rows

How do I remo开发者_开发百科ve duplicates from the table where all the columns are significant apart from PK.

declare @dummy table
(
   pk int,
   a char(1),
   b char(1),
   c char(1)
)

insert into @dummy 
select 1, 'A', 'B', 'B' union all
select 2, 'A', 'B', 'B' union all
select 3, 'P', 'Q', 'R' union all
select 4, 'P', 'Q', 'R' union all
select 5, 'X', 'Y', 'Z' union all
select 6, 'X', 'Y', 'Z' union all
select 7, 'A', 'B', 'Z' 

what I get with out distinction:

select * from @dummy

pk          a    b    c
----------- ---- ---- ----
1           A    B    B
2           A    B    B
3           P    Q    R
4           P    Q    R
5           X    Y    Z
6           X    Y    Z
7           A    B    Z

What I'd like is:

select ... do magic ....

pk          a    b    c
----------- ---- ---- ----
1           A    B    B
3           P    Q    R
5           X    Y    Z
7           A    B    Z

---

Found it:

select min(pk), a, b, c 
from @dummy 
group by a, b, c

---

You want something like this, I think:

DELETE FROM f
FROM @dummy AS f INNER JOIN @dummy AS g
     ON g.data = f.data
          AND f.id < g.id

Check out this article: http://www.simple-talk.com/sql/t-sql-programming/removing-duplicates-from-a-table-in-sql-server/

---

At first, I thought distinct would do it, but I'm fairly certain what you want is group by:

select * from @dummy group by a,b,c

Since there's a unique primary key, all rows are distinct.