April 11, 2011 at 12:29 pm
Hello, I'm trying to get multiple columns from a subquery based on a query. The problem is that the only way to get it that I've found until now is running the subquery as a field multiple times and using the top1 and order by to get the right row.
My English is not the best, but I believe that you'll be able to understand me.
I'm trying to find a better way to do that.
Here's my example:
CREATE TABLE #BUY (ID INT, COST MONEY, DATE DATETIME, VEHICLE INT, TAXES MONEY)
INSERT INTO #BUY (ID,COST,DATE, VEHICLE, TAXES) VALUES ( 1 , 10000,'2011-03-10',1 ,1300)
INSERT INTO #BUY (ID,COST,DATE, VEHICLE, TAXES) VALUES ( 2 , 1000,'2011-03-10',2 ,200)
INSERT INTO #BUY (ID,COST,DATE, VEHICLE, TAXES) VALUES ( 3 , 15000,'2011-03-12',1,350 )
CREATE TABLE #SELL (ID INT, PRICE MONEY, DATE DATETIME, VEHICLE INT)
INSERT INTO #SELL (ID,PRICE,DATE,VEHICLE) VALUES (1,11000, '2011-03-11', 1 )
INSERT INTO #SELL (ID,PRICE,DATE,VEHICLE) VALUES (2,5000, '2011-03-15', 2 )
INSERT INTO #SELL (ID,PRICE,DATE,VEHICLE) VALUES (3,20000, '2011-03-25', 1 )
The only way that I can get the Sells and within the sells query I get the Cost and the Taxes of the last time that I bought that vehicle is like this:
SELECT SELL.*,
ISNULL((SELECT TOP 1 COST FROM #BUY BUY WHERE SELL.VEHICLE = BUY.VEHICLE AND SELL.DATE > BUY.DATE ORDER BY DATE DESC),0 ) COST,
ISNULL((SELECT TOP 1 taxes FROM #BUY BUY WHERE SELL.VEHICLE = BUY.VEHICLE AND SELL.DATE > BUY.DATE ORDER BY DATE DESC),0 ) TAXES_BUY,
ISNULL((SELECT MAX(DATE) FROM #BUY BUY WHERE SELL.VEHICLE = BUY.VEHICLE AND SELL.DATE > BUY.DATE ),0 ) DATE_BUY
FROM #SELL SELL
I know that using the Top and the order by date desc is really expensive to the performance, can someone help me improve this query?
Thanks a lot.
April 11, 2011 at 12:49 pm
How about this?
; WITH CTE AS
(
SELECT SELL.*, BUY.COST , BUY.TAXES , BUY.DATE BUY_DATE
, RN = ROW_NUMBER() OVER( PARTITION BY BUY.VEHICLE ORDER BY BUY.DATE DESC)
FROM #SELL SELL
INNER JOIN #BUY BUY
ON SELL.VEHICLE = BUY.VEHICLE
AND SELL.DATE > BUY.DATE
)
SELECT *
FROM CTE
WHERE RN = 1
April 11, 2011 at 12:56 pm
You have a couple of choices. In this case, a CROSS APPLY is probably going to perform best, but you may find that a CTE with ROW_NUMBER() will perform better depending on a number of factors. You'll probably want to test both. Here is the CROSS APPLY version:
SELECT *
FROM #SELL
CROSS APPLY (
SELECT TOP 1 Cost, Taxes, Date AS Date_Buy
FROM #BUY
WHERE #Sell.Vehicle = #Buy.Vehicle
AND #SELL.DATE > #BUY.DATE
ORDER BY #BUY.DATE DESC
) AS Buy
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 11, 2011 at 12:57 pm
And here's a version of the cross apply w/ row-number:
SELECT sell.*, buy.cost, buy.taxes, buy.date
FROM #SELL sell
CROSS APPLY (SELECT Vehicle, Date, Cost, Taxes,
RN = ROW_NUMBER() OVER (PARTITION BY VEHICLE ORDER BY date DESC)
FROM #BUY buy
WHERE sell.VEHICLE = buy.vehicle
AND sell.date > buy.date) buy
WHERE buy.rn = 1
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 11, 2011 at 2:14 pm
I'll try these solutions.
Thanks a lot for all the help =-D.
September 28, 2015 at 5:36 am
A Subquery or Inner query or Nested query is an uncertainty inside further SQL query & entrenched inside the WHERE section. A subquery is utilized in arrival data that will be utilized in the key query as a circumstance to additional confine the data to be reposed. Various Transact-SQL declarations that contain subqueries can be instead prepared as joins.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply