sql server mseq: Hi,

Select Email,count(*) as cnt from <tablename>
group by email
having count(*) >1


Thanks
Hari
SQL Server MVP

[quoted text, click to view]

hi all,

i want a sql stmt for displaying the total records in a table having the
multiple copies of a particular field say email .

so i want to display those records having same email in more than one
entries.

Another solution, if the table has a primary key column (say it's called
[pk]), that would let you see the full rows is

select * from yourTable
where exists (
select * from yourTable as T2
where T2.email = yourTable.email
and T2.pk <> yourTable.pk
)

If the column you are focusing on allows NULL as a value, you would
need to account for that as a special case, and it would likely
slow down the query.

select * from yourTable
where exists (
select * from yourTable as T2
where (
T2.email = yourTable.email
or (T2.email is null and yourTable.email is null)
)
and T2.pk <> yourTable.pk
)

Yet another possibility is

select * from yourTable
where email in (
select email from yourTable
group by email
having count(*) > 1
)

Steve Kass
Drew University
http://www.stevekass.com


[quoted text, click to view]