Cross Apply & Top Function not working together

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 & Top Function not working together

Author

Topic

Pedro Palmer
Starting Member

4 Posts

Posted - 2009-04-08 : 11:52:38

Hi can anyone help me with the below query:

Select
PA.Melon,
PA.Orange,
PA.Kiwi,
S.Apple,
DD.Pear,
CA.Banana

From dbo.Fruit As S
Inner Join dbo.Bowl As DD
On S.Pear = DD.Pear
Inner Join dbo.Tree as PA
On S.Orange = PA.Orange

CROSS APPLY

(Select Top (10)*
From dbo.Ripe As P
Where S.Starfish = P.Starfish
And S.Starfish_ID In (76,77,78,79)
And S.Date < getdate()
Order By S.Date Desc) As CA

Order By
CA.Banana,
S.Date Desc

I want to bring back the top 10 dates for each category however the 'Top (10)*' function does not seem to working and I am getting everything back. Any help would be much appreciated!!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-08 : 12:55:28

i am not sure pears grow on orange trees :) but try this .
declare a local table variable @crossapply insert into it and use is after the CROSS APPLY (join)
or do common table by doing

;With Cte AS (Select Top (10)*
From dbo.Ripe As P
Where S.Starfish = P.Starfish
And S.Starfish_ID In (76,77,78,79)
And S.Date < getdate()
Order By S.Date Desc)
Select
PA.Melon,
PA.Orange,
PA.Kiwi,
S.Apple,
DD.Pear,
CA.Banana

From dbo.Fruit As S
Inner Join dbo.Bowl As DD
On S.Pear = DD.Pear
Inner Join dbo.Tree as PA
On S.Orange = PA.Orange
CROSS APPLY
Cte As yaya

try something like that

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-08 : 12:58:47

>>does not seem to working and I am getting everything back

what specifically is "not working"? Are you getting less than 10 values or more than 10 or errors or what?

Be One with the Optimizer
TG

Pedro Palmer
Starting Member

4 Posts

Posted - 2009-04-08 : 16:17:59

Hi,

I haven't given yosiasz's method a try yet but basically in reply to TG I am getting more than 10 results per category and I want exactly 10.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-08 : 17:25:25

when you run it as is how many rows are returned total?
If you remove the CROSS APPLY and the "CA.Banana" from your query then how many rows are returned?

Be One with the Optimizer
TG

Pedro Palmer
Starting Member

4 Posts

Posted - 2009-04-09 : 04:49:08

If I run my query without Cross Apply and CA.Banana I get 53681 rows but with it I get 114 rows and I want 40 in total.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-09 : 08:07:52

I can't tell what's going on without knowing the table structures, data, and logical keys but If you want 40 rows total - 10 for each category - then you should adjust your main query so that without the cross apply you get 4 rows returned. Then adding the cross apply should give you 10 rows per each of the correlated column(s).

Be One with the Optimizer
TG

Pedro Palmer
Starting Member

4 Posts

Posted - 2009-04-09 : 08:51:29

Hi,
I managed to get it to work without a Cross Apply by instead using a Row_Number() Over (Partition By Banana Order By Date Desc) As 'RowNumber'.

I think I was in the completely wrong territory with the Cross Apply but thank you for all the replies anyway :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources