SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Correlated Joins Using "Apply"


Correlated Joins Using "Apply"

Author
Message
GregoryAJackson
GregoryAJackson
Old Hand
Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)

Group: General Forum Members
Points: 328 Visits: 506
Comments posted to this topic are about the item Correlated Joins Using "Apply"

Gregory A Jackson MBA, CSM
Michele Ballarin
Michele Ballarin
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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
Alex 469216
Alex 469216
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 84
it could be a descent article had it been written 2 years ago.
GregoryAJackson
GregoryAJackson
Old Hand
Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)

Group: General Forum Members
Points: 328 Visits: 506
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
wordracr
wordracr
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 155
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)
DavidSimpson
DavidSimpson
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1156 Visits: 1077
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 Blink , so I appreciate an article that reminds me of different ways of getting a job done.

David



Michael Clark-374593
Michael Clark-374593
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 49
What's is the performance hit from using the UDF?

Would this be something for OLTP, or just OLAP?
GregoryAJackson
GregoryAJackson
Old Hand
Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)Old Hand (328 reputation)

Group: General Forum Members
Points: 328 Visits: 506
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
MudLuck
MudLuck
SSC Veteran
SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)

Group: General Forum Members
Points: 257 Visits: 519
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.



Peter E. Kierstead
Peter E. Kierstead
SSC-Addicted
SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)SSC-Addicted (432 reputation)

Group: General Forum Members
Points: 432 Visits: 453
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search