Re: query for displaying non distinct 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


gopi srinivas wrote:

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.

*** Sent via Developersdex http://www.developersdex.com ***


.

Relevant Pages