Help with query
in [Programming SQL]
|
Prev: Qs re CTE
Next: Interested in SQL Server Audit Tool
From: SAL on 20 Apr 2010 17:36 Thank you for your reply Plamen. I wound up with the following solution, not sure if it's the most efficient but..... SELECT AnnId FROM AffectedEasementsGASB WHERE TransfersAtAnn = 1 AND AnnId NOT IN (SELECT AnnId FROM AffectedEasementsGASB WHERE TransfersAtAnn IS NULL GROUP BY AnnId HAVING Count(AnnId) > 0) GROUP BY AnnId HAVING Count(AnnId) > 0)) S "Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message news:yL-dnXCwheojgVPWnZ2dnUVZ_qI7AAAA(a)speakeasy.net... > Here is one solution: > > SELECT AnnId > FROM AffectedEasementsGASB > GROUP BY AnnId > HAVING MAX(CAST(TransfersAtAnn AS INT)) = 1 > AND COUNT(TransfersAtAnn) = COUNT(*); > > -- > Plamen Ratchev > http://www.SQLStudio.com
From: Plamen Ratchev on 20 Apr 2010 17:44 The solution that I posted has a better execution plan that the one you posted. But always best to test with your real data. -- Plamen Ratchev http://www.SQLStudio.com
From: SAL on 20 Apr 2010 18:24 Hmmm, it does work. I just tested it. :) S P.S. still trying to understand it. "Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message news:yL-dnXKwhequvVPWnZ2dnUVZ_qI7AAAA(a)speakeasy.net... > The solution that I posted has a better execution plan that the one you > posted. But always best to test with your real data. > > -- > Plamen Ratchev > http://www.SQLStudio.com
From: Plamen Ratchev on 20 Apr 2010 21:29 The HAVING clause predicates implement the logic. The MAX predicate guarantees there is at least one with value 1. COUNT(TransfersAtAnn) is count excluding NULLs and COUNT(*) is count including NULLs, if they are equal then there are no NULLs. -- Plamen Ratchev http://www.SQLStudio.com
From: SAL on 21 Apr 2010 13:14
That's very close to what I thought. Thank you for your explanation Plamen... S "Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message news:JqOdnb_ks8FzyVPWnZ2dnUVZ_s6dnZ2d(a)speakeasy.net... > The HAVING clause predicates implement the logic. The MAX predicate > guarantees there is at least one with value 1. COUNT(TransfersAtAnn) is > count excluding NULLs and COUNT(*) is count including NULLs, if they are > equal then there are no NULLs. > > -- > Plamen Ratchev > http://www.SQLStudio.com |