newbie - Display rows with identical values

Hello,
how can i retrieve duplicate records?
I have this table with about 6500 records, and i know that there are a few
where a combination (Column1 - Column2) is identical
how can i retrieve these?SELECT Column1, Column2
FROM YourTable
GROUP BY Column1 - Column2
HAVING COUNT(*) > 1
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"benoit" <benoit@.discussions.microsoft.com> wrote in message
news:2851EFFB-2722-47E6-A654-D953AA4FB86A@.microsoft.com...
> Hello,
> how can i retrieve duplicate records?
> I have this table with about 6500 records, and i know that there are a few
> where a combination (Column1 - Column2) is identical
> how can i retrieve these?
>
>|||hi benoit,
Just a question, your table own a indentity field?
If so, use this:
delete from table1
where <identityfield> not in
(select max(<identityfiedl> ) from table1
group by [<field1>,<field2>]
Otherwise, let me know or post DDL
regards,
"benoit" wrote:

> Hello,
> how can i retrieve duplicate records?
> I have this table with about 6500 records, and i know that there are a few
> where a combination (Column1 - Column2) is identical
> how can i retrieve these?
>
>|||i was thinking the same way as roji
use northwind
select lastname,firstname into x from employees
union all
select top 5 lastname,firstname from employees
go
select lastname, firstname from x
group by lastname,firstname
having count(*)>1
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"benoit" wrote:

> Hello,
> how can i retrieve duplicate records?
> I have this table with about 6500 records, and i know that there are a few
> where a combination (Column1 - Column2) is identical
> how can i retrieve these?
>
>|||works great !
thx
"Roji. P. Thomas" wrote:

> SELECT Column1, Column2
> FROM YourTable
> GROUP BY Column1 - Column2
> HAVING COUNT(*) > 1
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "benoit" <benoit@.discussions.microsoft.com> wrote in message
> news:2851EFFB-2722-47E6-A654-D953AA4FB86A@.microsoft.com...
>
>|||Hi
This article 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
"benoit" <benoit@.discussions.microsoft.com> wrote in message
news:2851EFFB-2722-47E6-A654-D953AA4FB86A@.microsoft.com...
> Hello,
> how can i retrieve duplicate records?
> I have this table with about 6500 records, and i know that there are a few
> where a combination (Column1 - Column2) is identical
> how can i retrieve these?
>
>