Grabbing a non-unique field while using a group by

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 2000 Forums

Transact-SQL (2000)

Grabbing a non-unique field while using a group by

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-12 : 09:10:05

David writes "I am searching through a table that may have duplicate names. I am using a group by on 4 fields. I need to include a unique id along with the list of duplicates. How do I join this unique id that belongs to the name of the potential duplicates within the group by SQL statement? Code:

select NameInformation_Individuals.lastname, NameInformation_Individuals.firstname,
Disease.name,Patient_Case.MMWRYear, Patient_Case.Age, count(*)
from Disease INNER JOIN
Patient_Case ON Disease.id = Patient_Case.disease_id INNER JOIN
NameInformation_Individuals ON Patient_Case.NameID = NameInformation_Individuals.NameID
WHERE Patient_Case.MMWRYear = 2001
group by NameInformation_Individuals.lastname, NameInformation_Individuals.firstname,Disease.name, Patient_Case.MMWRYear, Patient_Case.Age
having count(*) > 1
order by count(*) desc, NameInformation_Individuals.lastname, NameInformation_Individuals.firstname"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-12 : 09:37:21

part of my solution in the following link should help.....

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=17644

A derived table should solve the problem.....you just need to change 2nd query to one which id's duplicates.........

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources