Correlated Joins Using "Apply"

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Gregory Jackson » Correlated Joins Using "Apply"

50 posts, Page 1 of 5

1 2 3 4 5 »»»

Correlated Joins Using "Apply"

Rate Topic

Display Mode

Topic Options

Author

Message

GregoryAJackson

Posted Saturday, June 14, 2008 3:02 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 26, 2013 2:11 PM
Points: 108, Visits: 485

Comments posted to this topic are about the item Correlated Joins Using "Apply"

Gregory A Jackson MBA, CSM

Post #517198

Michele Ballarin

Posted Monday, June 16, 2008 2:43 AM

Forum 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

Alex 469216

Posted Monday, June 16, 2008 6:49 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, December 14, 2012 10:47 AM
Points: 31, Visits: 72

it could be a descent article had it been written 2 years ago.

Post #517503

GregoryAJackson

Posted Monday, June 16, 2008 7:02 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 26, 2013 2:11 PM
Points: 108, Visits: 485

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

ryan.betker

Posted Monday, June 16, 2008 7:35 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 25, 2013 7:54 AM
Points: 83, Visits: 118

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

DavidSimpson

Posted Monday, June 16, 2008 7:53 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:29 AM
Points: 889, Visits: 931

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

Post #517566

Michael Clark-374593

Posted Monday, June 16, 2008 8:15 AM

Forum 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

GregoryAJackson

Posted Monday, June 16, 2008 8:31 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 26, 2013 2:11 PM
Points: 108, Visits: 485

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

MudLuck

Posted Monday, June 16, 2008 9:10 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 5:32 PM
Points: 125, Visits: 453

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

Peter E. Kierstead

Posted Monday, June 16, 2008 9:15 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 25, 2013 8:37 AM
Points: 163, Visits: 275

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 »

50 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions