Subquery issue locating a duplicate

1

This is the data:

last_name first_name id_no Race
Carlos Pat 45 07-White
Anderson EJ 8 03-Black or African American
Inplace Mom 9 08-Other Race
Inplace Teen 10 08-Other Race
Ball Sumu 18 07-White
Ball Sumu 18 07-White

in
i would only be interested Ball Sumu as it is a duplicate. there are 12000 rows
i have this code but is faulty.
select ac.last_name, ac.first_name, ac.id_no, ri.description as race from all_clients_view ac
inner join race ra
on ac.people_id = ra.people_id
inner join race_info ri
on ra.race_info_id = ri.race_info_id
where ac.people_id in (select ce.people_id from race ce)
order by id_no

2 
create table #pk400i(last_name varchar(50),	
first_name varchar(50),	
id_no varchar(50),	
Race varchar(50))


insert into #pk400i
select 'Carlos',	'Pat',	45,	'07-White' UNION all
select 'Anderson',	'EJ',	8,	'03-Black or African American' UNION all
select 'Inplace',	'Mom',	9,	'08-Other Race' UNION all
select 'Inplace',	'Teen',	10,	'08-Other Race' UNION all
select 'Ball',	'Sumu',	18,	'07-White' UNION all
select 'Ball',	'Sumu',	18,	'07-White'  

;with src
as
(
select * , 
ROW_NUMBER() OVER(PARTITION BY id_no ORDER BY id_no ASC)  dedup
From #pk400i
)
select first_name, last_name,id_no,Race
 from src 
 where dedup = 1
drop table #pk400i
3
create data script

-- Create Tables
create table SQLTeam.TempTable(last_name varchar(100), first_name varchar(100), id_no int , Race varchar(100) )

-- Insert data Tables

insert into SQLTeam.TempTable select 'Carlos', 'Pat', 45 ,'07-White'
insert into SQLTeam.TempTable select 'Anderson','EJ', 8 ,'03-Black or African American'
insert into SQLTeam.TempTable select 'Inplace', 'Mom', 9 ,'08-Other Race'
insert into SQLTeam.TempTable select 'Inplace', 'Teen', 10 ,'08-Other Race'
insert into SQLTeam.TempTable select 'Ball', 'Sumu', 18 ,'07-White'
insert into SQLTeam.TempTable select 'Ball', 'Sumu', 18 ,'07-White'

select   
     'SQL'
     ,id_no
	 ,last_name
	 ,first_name
	 ,count(*) 
from 
    SQLTeam.TempTable 
group by  
    id_no,last_name,first_name 
having 
    count(*) > 1

image

1 Like