Question：Can you use a SQL statement to finding duplicate values!
How can I find authors with the same last name?
You can use the table authors in datatabase pubs. I want to get the result as below:
Output:
au_lname number_dups
---------------------------------------- -----------
Ringer 2
(1 row(s) affected)
Answer：
if OBJECT_ID('dbo.Table101302') is not null
drop table dbo.Table101302
create table dbo.Table101302
(
[Id] int,
[au_lname] varchar(10)
)
insert into dbo.Table101302 values(1, 'name1')
insert into dbo.Table101302 values(2, 'name2')
insert into dbo.Table101302 values(3, 'name3')
insert into dbo.Table101302 values(4, 'name3')
insert into dbo.Table101302 values(5, 'name4')
insert into dbo.Table101302 values(6, 'name5');
insert into dbo.Table101302 values(7, 'name5');
select [au_lname], [number_dups]=COUNT(1)
from dbo.Table101302
group by [au_lname]
--having(COUNT(1)>1)
drop table dbo.Table101302
