Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Correlated Joins Using "Apply" Expand / Collapse
Author
Message
Posted Saturday, June 14, 2008 3:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 29, 2014 12:39 PM
Points: 110, Visits: 495
Comments posted to this topic are about the item Correlated Joins Using "Apply"

Gregory A Jackson MBA, CSM
Post #517198
Posted Monday, June 16, 2008 2:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 16, 2011 2:04 AM
Points: 1, Visits: 30
i'm unable to read it
i try to turn the video, but SQLscritp turn whit the screen and the only way to read the script is to copy and paste into Notepad or similar....

Michele
Post #517399
Posted Monday, June 16, 2008 6:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 18, 2013 11:44 AM
Points: 31, Visits: 75
it could be a descent article had it been written 2 years ago.
Post #517503
Posted Monday, June 16, 2008 7:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 29, 2014 12:39 PM
Points: 110, Visits: 495
Alex,
I wish I had known of this feature 2 years ago but it's one I missed until recently. My hope is that there are others out there that had been missing this also so now they can leverage it.


Cheers,

GAJ


Gregory A Jackson MBA, CSM
Post #517516
Posted Monday, June 16, 2008 7:35 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 15, 2014 12:54 AM
Points: 83, Visits: 126
This is possible in sql 2000 as well.

select 1 as id, '1st' as position into #data union all
select 1 as id, '2nd' as position union all
select 1 as id, '3rd' as position union all
select 1 as id, '4th' as position union all
select 1 as id, '5th' as position union all
select 2 as id, '1st' as position union all
select 2 as id, '2nd' as position union all
select 2 as id, '3rd' as position union all
select 2 as id, '4th' as position union all
select 2 as id, '5th' as position

select *
from #data d1
where position in (select top 3 position from #data d2 where d1.id = d2.id order by position)
Post #517552
Posted Monday, June 16, 2008 7:53 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:39 AM
Points: 916, Visits: 991
I disagree with Alex, it does not matter if SQL Server 2005 was release 2 minutes ago or 2 years ago, there is always something new to learn about SQL Server. I think this was a good article pointing out a feature introduced in SQL Server 2005. I have read about APPLY but have not used it yet which makes it easily forgettable , so I appreciate an article that reminds me of different ways of getting a job done.

David



Post #517566
Posted Monday, June 16, 2008 8:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 23, 2009 12:38 PM
Points: 1, Visits: 48
What's is the performance hit from using the UDF?

Would this be something for OLTP, or just OLAP?
Post #517580
Posted Monday, June 16, 2008 8:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 29, 2014 12:39 PM
Points: 110, Visits: 495
Hi Michael,
I'm not sure of the performance hit of the udf, I'd have to test the various query plans, etc.

The maintenace issue is what I'd want to stay away from (Having to create another object in SQL, Version control it, script it, maintain it, etc)



GAJ


Gregory A Jackson MBA, CSM
Post #517589
Posted Monday, June 16, 2008 9:10 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, July 12, 2014 5:11 PM
Points: 126, Visits: 499
Hmm,

The first thought that came to me isn't this what I've use derived tables and identities for in the past.

I won't guarantee performance want suffer of be less then using apply because I'm still on sql 2000 :-(, but this is how I would solve the problem.


In the derived table query I would show the transaction id on the top three items and there dates sorted by transaction id and dates I would then have another query above it in the same derived table that mapped those rows to there identities and then use those identities as the join condition out of the derived table to return my max top 3 rows per transaction ID.

I haven't seen your schema put I'm pretty sure this can be done.

Enjoy.



Post #517631
Posted Monday, June 16, 2008 9:15 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 20, 2014 2:22 PM
Points: 190, Visits: 436
We sometimes create our own "full-Text" indexes instead of using SQL's Full-Text feature because we have non-standard requirements...

I'll create a Multi-Statement table-valued function that parses a string parameter into a table of terms.

I'll Cross Apply this function to a database table passing the column (or appended columns) I wish to parse into a full-text index, outputing the resultant UDF-generated table along with the primary key of the database table row.

I'll index this table appropriately, and wallah!, home-grown full-text index...

Tastes great... less filling...

I leave the exercise of integration up to you to help burn the calories gained from ingesting this full-bodied brew!




PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Post #517635
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse