Cross Apply, or sub query, or other? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cross Apply, or sub query, or other?

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-07-02 : 09:35:15

I am having difficulty trying to find the best practice for this scenerio.

I want to get the top date that is lower than the date of that row. Here is 3 samples that all give me the same results, and I do not really like any of them. Whichi is normally the best practice, I will assume the row number would normally be best, but there are a lot more than 3 records in a live scenerio.

Yes, I know to checkt he execution plans, but I am more interested in finding the best practice for this (whether it is listed below, or if there is another method someone is aware of).

Thanks



declare @MyDates Table(d datetime,v char(1))
insert into @MyDates(d,v)
select '01/01/2005','A' Union all
select '01/01/2007','B' Union all
select '01/30/2007','C'



Declare @Myvalues table (d datetime,col1 varchar(5))
Insert Into @Myvalues(d,Col1)
select '01/01/2006','John' Union all
select '01/01/2009','Tom' Union all
select '01/15/2007','Georg'


SELECT d,col1,v
FROM 
(
Select row_Number() over ( Partition by a.Col1 order by b.d desc) as rowID,a.*,b.v from
@MyValues a
Inner join
@Mydates b
on a.d > b.d
) AA
WHERE AA.rOWid = 1


Select a.*,(Select top 1 v from @Mydates where d < a.d order by d desc) as v
from @MyValues a

select a.* ,b.v
from @MyValues a
cross apply
(Select top 1 * from @MyDates aa where aa.d <a.d  order by aa.d desc) b

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-02 : 09:39:03

It's mainly an issue of present indexes and number of records in each table.
The number of sample records is too small to measure either with Actual Execution Plan or SQL Profiler.

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources