Duplicates in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Duplicates in SQL

Author

Topic

raptor260
Starting Member

7 Posts

Posted - 2009-12-17 : 15:36:39

I have a task that is kicking my butt. I have a table called [patient information] and in this table there are 30 columns. There are columns that I am working in, guarID and patientid.
PatientID is a unique field that assigns a number automatically. guarID is a provided number that could be the same. I hope I have explained this well enough. Okay here is what I need to do, I need to query the patient information table for all records that have duplicate groupid numbers, but I onyl need to see the ones what have the smallest patientid number.
Example
GuarID PatientID
321027 100546
321027 100607
321027 100612
329626 100550
329626 100582
320481 100552
320481 106379

I only need to see the records that have the smallest patientid number that have duplicate guarid's.

Here is the query that I have so far.

SELECT [Patient Information].GroupID, [Patient Information].[Group Leader], [Patient Information].GuarID
FROM [Patient Information]
WHERE ((([Patient Information].GroupID) In (SELECT [GroupID] FROM [Patient Information] As Tmp GROUP BY [GroupID] HAVING Count(*)>1 )))
ORDER BY [Patient Information].GroupID, [Patient Information].PatientID;

Any help would be greatly appreciated.
Thank You

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-12-17 : 16:06:55

SELECT
a.GruarID
,Min(a.PatientID) as PatientID
FROM
[Patient Information] a
group by a.GuarID
having Count(*) > 1

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

raptor260
Starting Member

7 Posts

Posted - 2009-12-17 : 17:33:20

I think it will work but it only returns the 2 columns. I will need it to return more than just those 2 columns. I will need to see data from these fields
GroupID,
[Group Leader],
GuarID,
PatientID,
FirstName,
MiddleName,
[Guarantor First Name],
[Guarantor Last Name],
Address,
City,
State,
ZipCode,
ReferredBy,
ProviderAcctNumber

But filter out the guarid duplicates and lowest patientid.

I hope that makes since.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-12-17 : 19:14:12

Try this

with candidateRecs (GuarID,PAtientID) as
(
SELECT
a.GuarID
,Max(a.PatientID) as PatientID
FROM
[Patient Information] a
group by a.GuarID
having Count(*) > 1
)
select
GroupID,
[Group Leader],
GuarID,
PatientID,
FirstName,
MiddleName,
[Guarantor First Name],
[Guarantor Last Name],
Address,
City,
State,
ZipCode,
ReferredBy,
ProviderAcctNumber

From [Patient Information] A
join candidateRecs B on A.GuarID = B.GuarID and A.PatientID = B.PatientID

An infinite universe is the ultimate cartesian product.

raptor260
Starting Member

7 Posts

Posted - 2009-12-17 : 19:22:06

When I run that query this is what I get back

Msg 209, Level 16, State 1, Line 14
Ambiguous column name 'GuarID'.
Msg 209, Level 16, State 1, Line 15
Ambiguous column name 'PatientID'.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-12-17 : 23:08:30

quote:
Originally posted by cat_jesus

Try this

with candidateRecs (GuarID,PAtientID) as
(
SELECT
a.GuarID
,Max(a.PatientID) as PatientID
FROM
[Patient Information] a
group by a.GuarID
having Count(*) > 1
)
select
a.GroupID,
a.[Group Leader],
a.GuarID,
a.PatientID,
a.FirstName,
a.MiddleName,
a.[Guarantor First Name],
a.[Guarantor Last Name],
a.Address,
a.City,
a.State,
a.ZipCode,
a.ReferredBy,
a.ProviderAcctNumber

From [Patient Information] A
join candidateRecs B on A.GuarID = B.GuarID and A.PatientID = B.PatientID

An infinite universe is the ultimate cartesian product.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-12-17 : 23:13:45

I'm typing this from my phone, so It's too difficult typing out the query, but you can easily accomplish this by using the row_number() function as well. If you want to see that method and you have trouble figuring it out, let me know and I'll post it tomorrow when I'm at a computer.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

raptor260
Starting Member

7 Posts

Posted - 2009-12-17 : 23:59:16

I think this is over my head so any help would be great.

Thank You

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-12-18 : 03:07:03

Here are some other method. for more columns just using a Derived Table and Inner Join.

declare @Patient_Information table
(GuarID int,PatientID int, unique(PatientID))

insert into @Patient_Information
values 
(321027, 100546),
(321027, 100607),
(321027, 100612),
(329626, 100550),
(329626, 100582),
(320481, 100552),
(320481, 106379),
(123123, 423423)

--Method 1
SELECT a.GuarID
	,Min(a.PatientID) as PatientID
FROM @Patient_Information a
group by a.GuarID
having Count(*) > 1

--Method 2
select a.GuarID, a.PatientID
from @Patient_Information a
where (select count(*)
		from @Patient_Information b
		where a.GuarID = b.GuarID) > 1
 and a.PatientID in 
	 (select top 1 c.PatientID
	  from @Patient_Information as c
	  where a.GuarID = c.GuarID
	  order by c.GuarID asc)

--Method 3
;with c as
(select GuarID
from @Patient_Information
group by GuarID
having min(PatientID)<>max(PatientID))
select c.GuarID, b.PatientID
from c
cross apply 
(select top 1 a.PatientID
from @Patient_Information a
where c.GuarID = a.GuarID
order by a.PatientID) as b
 
--Method 4
select d.GuarID, d.PatientID
from
(select *, r=row_number() 
over(partition by GuarID order by PatientID asc)
from @Patient_Information) as d
where r=1 
and (select count(*)
	 from @Patient_Information b
	 where d.GuarID = b.GuarID) > 1

raptor260
Starting Member

7 Posts

Posted - 2009-12-18 : 11:57:54

I run this query here and it is doing everything I need except I need to add this but it won't let me. How can I add this extra where clause?

This is not work (Select *
FROM [Patient Information] INNER JOIN [Status Table] ON [Patient Information].Status = [Status Table].Status
where [Status Table].Active = 'True')

This works
select a.GuarID, a.PatientID,
GroupID,
[Group Leader],
FirstName,
MiddleName,
[Guarantor First Name],
[Guarantor Last Name],
Address,
City,
State,
ZipCode,
ReferredBy,
ProviderAcctNumber
FROM [Patient Information] a
where (select count(*)
from [Patient Information] b
where a.GuarID = b.GuarID) > 1
and a.PatientID in
(select top 1 c.PatientID
from [Patient Information] as c
where a.GuarID = c.GuarID
order by c.GuarID asc)

raptor260
Starting Member

7 Posts

Posted - 2009-12-21 : 11:58:18

Hey Guys, can I get a little help.

Thank You

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-12-21 : 13:30:15

[code]
Select
a.GroupID,
a.[Group Leader],
a.FirstName,
a.MiddleName,
a.[Guarantor First Name],
a.[Guarantor Last Name],
a.Address,
a.City,
a.State,
a.ZipCode,
a.ReferredBy,
a.ProviderAcctNumber
FROM
[Patient Information] a
INNER JOIN
(
select
aa.GuarID
,Min(aa.PatientID) as PatientID
From
[Patient Information] aa
INNER JOIN
[Status Table] bb
ON aa.Status = bb.Status
where bb.Active = 'True'
Group By aa.GruarID
Having Count(*) > 1
) b
on a.GuarID = b.GuarID
and a.PatientID =b.PatientID
[/code]

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources