find duplicate entries in lastName field.

hey Everyone.
Could anyone give show me a query that would return just the records from a
table that have more than just one of a last name.
Thanks
--
'If it looks like your going to bite it, try not to ruin the shoot' -
stuntman credoFor such problems always post your table structures, sample data & expected
results. For details refer to : www.aspfaq.com/5006
Here is a solution based on guesswork:
SELECT * -- use column names
FROM tbl t1
WHERE t1.lastname IN (
SELECT t2.lastname
FROM tbl t2
GROUP BY t2.lastname
HAVING COUNT( * ) > 1 ) ;
--
Anith|||Example:
use northwind
go
select employeeid, firstname, lastname
into t
from dbo.employees
go
insert into t (firstname, lastname)
select firstname, lastname
from dbo.employees
where employeeid = 7
go
select
e1.employeeid,
e1.firstname,
e1.lastname
from
dbo.t as e1
inner join
(
select
lastname
from
dbo.t
group by
lastname
having
count(*) > 1
) as e2
on e1.lastname = e2.lastname
order by
e1.employeeid
go
drop table t
go
AMB
"kahunaVA" wrote:
> hey Everyone.
> Could anyone give show me a query that would return just the records from a
> table that have more than just one of a last name.
> Thanks
> --
> 'If it looks like your going to bite it, try not to ruin the shoot' -
> stuntman credo|||Thanks Anith, Gave me the basis of my solution.
"Anith Sen" wrote:
> For such problems always post your table structures, sample data & expected
> results. For details refer to : www.aspfaq.com/5006
> Here is a solution based on guesswork:
> SELECT * -- use column names
> FROM tbl t1
> WHERE t1.lastname IN (
> SELECT t2.lastname
> FROM tbl t2
> GROUP BY t2.lastname
> HAVING COUNT( * ) > 1 ) ;
> --
> Anith
>
>