CROSS APPLY

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » CROSS APPLY

CROSS APPLY

Rate Topic

Display Mode

Topic Options

Author

Message

Minnu

Posted Wednesday, October 17, 2012 6:15 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:03 AM
Points: 119, Visits: 361

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

Post #1373755

Andy Hyslop

Posted Wednesday, October 17, 2012 7:50 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 7:54 AM
Points: 595, Visits: 2,133

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

Jeff Moden

Posted Wednesday, October 17, 2012 8:30 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:33 PM
Points: 32,902, Visits: 26,783

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1373839

drew.allen

Posted Wednesday, October 17, 2012 9:26 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 1:20 PM
Points: 1,235, Visits: 5,389

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

Post #1373896

« Prev Topic | Next Topic »

Permissions