Subquery returning more than one value

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

General SQL Server Forums

New to SQL Server Programming

Subquery returning more than one value

Author

Topic

Longy
Starting Member

5 Posts

Posted - 2009-12-01 : 06:47:36

I have this update query

UPDATE DataSet_PL_W SET PrimaryTreatment = (
SELECT Distinct dom.Value
FROM ClinicalSolutions.CallData_Fact call
inner join ClinicalSolutions.DataItemDefinition_Dim did on call.DataItemDefinitionId = did.DataItemDefinitionId
inner join ClinicalSolutions.DomainValue_Fact dom on dom.domainId = call.DomainId and dom.ValueId = call.valueId
INNER JOIN

(SELECT call1.Callid as 'Call1', did.DataItemDefinitionId, Max(call1.CallDataItemEnumeratedModifiedAt) as maxd
FROM ClinicalSolutions.CallData_Fact call1
INNER JOIN ClinicalSolutions.DataItemDefinition_Dim did on call1.DataItemDefinitionId = did.DataItemDefinitionId
WHERE call1.DataItemDefinitionId = '317'
GROUP BY call1.CallId, did.DataItemDefinitionId) as subquery

ON subquery.Call1 = call.CallId AND subquery.maxd = Call.CallDataItemEnumeratedModifiedAt
WHERE did.DataItemDefinitionId = '317' AND Call.CallId = DataSet_PL_W.VisitId)

This query returns the latest domian value for all the callid's that have a value entered. the problem that I am having is that the date is the same for more than one record. I have gotten around a lot by using select distinct however there are a few that have different values entered at the same time. below is an example of what is returned and i only need to select one of them I'm not worried which one

1016 Bloods taken 2006-09-12 14:55:57.980
1016 Blood taken 2006-09-12 14:55:57.980

Any help would be most appreciated

Thanks

Longy

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-01 : 07:12:51

select top 1

No, you're never too old to Yak'n'Roll if you're too young to die.

Longy
Starting Member

5 Posts

Posted - 2009-12-01 : 07:29:40

Thanks Webfred,

but the way I have been using Select Top 1 only returns one row, I require 1 row for every callId is there a way to do this with select top 1 (am I missing something)

Thanks again

Longy

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-01 : 08:16:19

Your subquery goes on Call.CallId = DataSet_PL_W.VisitId so where is the problem?

You can make a select instead of an update to see what would happen...

No, you're never too old to Yak'n'Roll if you're too young to die.

Longy
Starting Member

5 Posts

Posted - 2009-12-01 : 09:00:04

sorry webfred you are right I just couldn't get the select top 1 to work as a select but it does work in the update statement

thank you

Longy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources