Hoe to Find Duplicate values?

I have a table with no index, When i try to create one to Check data
integrity on 3 column that suppose they are unique collection
(Court_ID,Case_No,Case_Date ) i failed due to duplicated values..
I know i may ignore duplicate values but i want to find it to delete it..
So How could i write query to find duplicate rows
Select Court_ID,Case_No,Case_Date from Case Where...'This script had written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Islamegy" <NULL_Islamegy_NULL@.yahoo.com> wrote in message
news:%23FjhHd17FHA.2616@.TK2MSFTNGP15.phx.gbl...
>I have a table with no index, When i try to create one to Check data
>integrity on 3 column that suppose they are unique collection
>(Court_ID,Case_No,Case_Date ) i failed due to duplicated values..
> I know i may ignore duplicate values but i want to find it to delete it..
> So How could i write query to find duplicate rows
> Select Court_ID,Case_No,Case_Date from Case Where...'
>|||Islamegy wrote:
> I have a table with no index, When i try to create one to Check data
> integrity on 3 column that suppose they are unique collection
> (Court_ID,Case_No,Case_Date ) i failed due to duplicated values..
> I know i may ignore duplicate values but i want to find it to delete
> it..
> So How could i write query to find duplicate rows
> Select Court_ID,Case_No,Case_Date from Case Where...'
select Court_ID,Case_No,Case_Date, count(*)
group by Court_ID,Case_No,Case_Date
having count(*) > 1
Kind regards
robert