1 DELETE문

2 조언

--샘플 테이블 생성
CREATE TABLE MEMBER( 
	IDX int identity(1,1)
,	NAME Char(20) 
)
GO

--샘플 데이터 입력
INSERT INTO MEMBER VALUES('A') 
INSERT INTO MEMBER VALUES('A') 
INSERT INTO MEMBER VALUES('B') 
INSERT INTO MEMBER VALUES('B') 
INSERT INTO MEMBER VALUES('B') 
INSERT INTO MEMBER VALUES('B') 
INSERT INTO MEMBER VALUES('C') 
INSERT INTO MEMBER VALUES('C')
GO

--2000이하 버전
DELETE FROM MEMBER 
WHERE IDX IN ( 
	SELECT A.IDX as IDX 
	FROM MEMBER A INNER JOIN (
			SELECT 
				MAX(IDX) AS IDX
			,	NAME AS NAME 
			,	COUNT(*)  AS CNT 
			FROM MEMBER 
			GROUP BY NAME 
			HAVING COUNT(*) >1) B
	ON A.NAME = B.NAME  
	AND A.IDX!=B.IDX 
)

--2005이상 버전(위 솔루션보다 훨씬 적은 비용으로 처리할 수 있다)
DELETE A
FROM 
(
	SELECT 
		IDX
	,	NAME
	,	ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY IDX) NUM
	FROM MEMBER
) A
WHERE NUM <> 1