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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy