Post reply

How to use count over partition with cte instead of using having ?

  • April 3, 2022 at 1:20 pm

    #4010058

    I work on sql server 2017 i have issue i can't use count over partition instead of using having distinct

    main statment i use

     

     SELECT P.PartsFamilyID, P.PackageID, R.RoHSStatus 
        
     FROM Parts.ManufacturingData M WITH(NOLOCK)
     INNER JOIN Parts.Nop_Part P WITH(NOLOCK) ON P.PartID = M.PartID
     INNER JOIN Parts.ROHS R WITH(NOLOCK) ON R.ZPartID = P.PartID
     where packageId is not null
     GROUP BY P.PartsFamilyID, P.PackageID, R.RoHSStatus
     HAVING COUNT(DISTINCT M.LeadFinishPlatingID) > 1 OR COUNT(DISTINCT M.BaseMaterialID) > 1

    i need exactly rewrite statment above using count over partition with cte

    with ctemanufacture as

    (

    )

    select * into #test from rn1 > 1 or rn2>1

    so please

    How can i do that please ?

  • April 3, 2022 at 3:44 pm

    #4010106

    What have you tried? This is you 171st post so you know what to do

  • April 4, 2022 at 2:42 am

    #4010235

    Easy now... in this case, the OP has pretty much stated what they want to do.  The problem with the HAVING is that it becomes difficult to select the rest of the row as in a SELECT *.  I believe the op is asking without the "try" because he doesn't actually know what to try but does know that it can overcome the limitations of GROUP BY/HAVING.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply