how to apply the CROS APPLY function

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)

how to apply the CROS APPLY function

Author

Topic

korssane
Posting Yak Master

104 Posts

Posted - 2010-06-23 : 11:54:24

hi guys , i am stuck with a query here..
i have a table with 5 columns ( col1,col2,..col5)
what i need is to pull all columns info where Col3 has a specific value " vallue3" and col1 values are upper than or equal the col1 value associated with "value3"

here is my code :

select
T1.col1,
T1.col2,
T1.col3,
FORM table1 T1
cross apply (
Top (1) T2.col2,T2.col3
FROM table1 T2 where (T2.col3 ='value3')
) T2

WHERE (T1.col2 >= T2.col2)
order by T1.col2, asc
;

any help will be appreciated.!!
thanks

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-06-23 : 12:47:58

may be this

select
T1.col1,
T1.col2,
T1.col3,
T2.col2,T2.col3
FORM table1 T1
cross apply (
Top (1) col2,col3
FROM table1 where col3 ='value3'
and T1.col2 >= col2
order by col2 asc
) T2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

korssane
Posting Yak Master

104 Posts

Posted - 2010-06-23 : 14:47:06

thanks Visakh16..

trying it and getting only the 1 st record correct ..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources