quote:

---

Originally posted by Kristen

Do you know if that is faster than ROW_NUMBER ... OVER? (I presume so, but have not tested it)

---

CREATE TABLE #Pricing
(
	P_Volume	int NOT NULL,
	P_Price		money NOT NULL,
	PRIMARY KEY
	(
		P_Volume
	)
)

INSERT INTO #Pricing(P_Volume, P_Price)
SELECT	100, 10.00 UNION ALL
SELECT	200, 20.00 UNION ALL
SELECT	300, 40.00

CREATE TABLE #Volume
(
	V_Client	varchar(20) NOT NULL,
	V_Volume	int NOT NULL,
	PRIMARY KEY
	(
		V_Client
	)
)

INSERT INTO #Volume(V_Client, V_Volume)
SELECT	'name1', 50 UNION ALL
SELECT	'name2', 135 UNION ALL
SELECT	'name3', 285

-- SET SHOWPLAN_TEXT ON
GO
-- SET STATISTICS IO ON; SET STATISTICS TIME ON

PRINT 'KHTan'
SELECT	*
FROM	#Volume
	CROSS APPLY
	(
		SELECT TOP 1 P_Price
		FROM	#Pricing
		WHERE	V_Volume <= P_Volume
		ORDER BY P_Volume 
	) AS P

/**
Table '#Pricing'. Scan count 3, logical reads 6
Table '#Volume'. Scan count 1, logical reads 2

Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[#Volume].[V_Volume]))
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#Volume]))
|--Top(TOP EXPRESSION:((1)))
    |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Pricing]), SEEK:([tempdb].[dbo].[#Pricing].[P_Volume] 
								>= [tempdb].[dbo].[#Volume].[V_Volume]) ORDERED FORWARD)
**/



GO
SET STATISTICS IO OFF; SET STATISTICS TIME OFF
GO
SET SHOWPLAN_TEXT OFF
GO
DROP TABLE #Volume
GO
DROP TABLE #Pricing
GO

;with cte
as
(
select *,ROW_NUMBER()over(order by P_volume)as id from #Pricing
),cte1
as
(
select *,ROW_NUMBER()over(order by V_volume)as id from #Volume
)
select V_Client,V_Volume,P_Price from cte C inner join cte1 C1 on C.id=C1.id