Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

CROSS APPLY Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2012 6:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 27, 2016 1:25 AM
Points: 293, Visits: 903


CROSS APPLY (
SELECT col = CASE WHEN MAX(col) = MIN(col) THEN MAX(col) ELSE NULL END
)
Post #1373755
Posted Wednesday, October 17, 2012 7:50 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 8:22 AM
Points: 697, Visits: 3,000
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
Post #1373797
Posted Wednesday, October 17, 2012 8:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 8:48 PM
Points: 40,994, Visits: 38,293
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1373839
Posted Wednesday, October 17, 2012 9:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:01 PM
Points: 1,962, Visits: 8,204
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



How to post data/code on a forum to get the best help.
Make sure that you include code in the appropriate IFCode tags, e.g. [code="sql"]<your code here>[/code]. You can find the IFCode tags on the left when you are writing a post.
How to Post Performance Problems
Post #1373896
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse