CROSS APPLY

  • CROSS APPLY (

    SELECT col = CASE WHEN MAX(col) = MIN(col) THEN MAX(col) ELSE NULL END

    )

  • And the question is!?

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • Skanda (10/17/2012)


    CROSS APPLY (

    SELECT col = CASE WHEN MAX(col) = MIN(col) THEN MAX(col) ELSE NULL END

    )

    You REALLY need to get out of the habit of editing your posts. Replacing the question with the answer does no one reading the thread any good. Even you won't have a clue as to what the question actually was in a couple of weeks so you've destroyed your own valuable reference.

    --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)

  • Skanda (10/17/2012)


    CROSS APPLY (

    SELECT col = CASE WHEN MAX(col) = MIN(col) THEN MAX(col) ELSE NULL END

    )

    CROSS APPLY applies a "function" to each row of the (virtual) table. In this case, you're calculating a value based on an aggregate of a set consisting of a single record. You can see this by adding a COUNT(*) to your SELECT clause within the CROSS APPLY. Because the set contains a single record, MAX(col) is necessarily equal to MIN(col) except for the case where col is null. Since you're returning a null value in that case, your CROSS APPLY is equivalent to

    CROSS APPLY ( SELECT col )

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

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

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