Join TOP n Rows

Is there a way to join tables using a TOP n attribute... or a technique
to accomplish this... I have ran into a need for this several times.
Example 1:
Use the most recent available exchange rate to calculate a dollar
amount (where we don't always have an exchange rate on a given day like
wends).
SELECT (t.Amount * e.Rate) AS DollarAmount
FROM Transactions t
INNER JOIN TOP 1 ExchangeRate e
ON e.TradeDate <= t.TradeDate
ORDER BY e.TradeDate DESC
Example 2:
Get the average of the previous 10 (or less) transactions for each
individual transaction.
SELECT t.Product, t.Amount, AVG(t2.Amount) AS HistoricalAverage
FROM Transaction t
INNER JOIN TOP 10 Transaction t2
ON t.Product = t2.Product AND t2.TradeDate <= t.TradeDate
ORDER BY t2.TradeDate DESC
GROUP BY t.Product, t.Amount
In order to get this to work... the INNER JOIN would need to support
the TOP and the ORDER BY keywords... I don't believe it does... any
thoughts on how to accomplish the results I'm looking for?
Thanks!
Brian Ricetry this untested as ddl is not posted
SELECT (t.Amount * e.Rate)AS DollarAmount FROM Transactions t, ExchangeRate
e
WHERE t.amount = (select Max(amount) from Transactions where Tradedate <=
e.TradeDate)-- there is only one max
OR
SELECT (t.Amount * e.Rate)AS DollarAmount FROM Transactions t, ExchangeRate
e
WHERE t.amount = (select TOP 1 amount from Transactions where Tradedate <=
e.TradeDate ORDER BY TradeDate DESC )
Regards
R.D
--Knowledge gets doubled when shared
"mail@.brianrice.com" wrote:

> Is there a way to join tables using a TOP n attribute... or a technique
> to accomplish this... I have ran into a need for this several times.
> Example 1:
> Use the most recent available exchange rate to calculate a dollar
> amount (where we don't always have an exchange rate on a given day like
> wends).
> SELECT (t.Amount * e.Rate) AS DollarAmount
> FROM Transactions t
> INNER JOIN TOP 1 ExchangeRate e
> ON e.TradeDate <= t.TradeDate
> ORDER BY e.TradeDate DESC
> Example 2:
> Get the average of the previous 10 (or less) transactions for each
> individual transaction.
> SELECT t.Product, t.Amount, AVG(t2.Amount) AS HistoricalAverage
> FROM Transaction t
> INNER JOIN TOP 10 Transaction t2
> ON t.Product = t2.Product AND t2.TradeDate <= t.TradeDate
> ORDER BY t2.TradeDate DESC
> GROUP BY t.Product, t.Amount
> In order to get this to work... the INNER JOIN would need to support
> the TOP and the ORDER BY keywords... I don't believe it does... any
> thoughts on how to accomplish the results I'm looking for?
> Thanks!
> Brian Rice
>|||For Example 1:
select (t.Amount * e.Rate) AS DollarAmount
FROM Transactions t
inner join ExchangeRate e on 1 = 1
inner join
(
SELECT t1.TradeDate as TTradeDate,max(e1.TradeDate) as ETradeDate
FROM Transactions t1
INNER JOIN ExchangeRate e1 ON e1.TradeDate <= t1.TradeDate
group by t1.TradeDate
) a on a.TTradeDate = t.TradeDate and a.ETradeDate = e.TradeDate
Example 2 I don't understand... What result are you want? Please, more
details...
endorsed by signature
*** Serg Yury ***
<mail@.brianrice.com> '?/'' ? '' '?:
news:1130125213.023275.300860@.g44g2000cwa.googlegroups.com...
> Is there a way to join tables using a TOP n attribute... or a technique
> to accomplish this... I have ran into a need for this several times.
> Example 1:
> Use the most recent available exchange rate to calculate a dollar
> amount (where we don't always have an exchange rate on a given day like
> wends).
> SELECT (t.Amount * e.Rate) AS DollarAmount
> FROM Transactions t
> INNER JOIN TOP 1 ExchangeRate e
> ON e.TradeDate <= t.TradeDate
> ORDER BY e.TradeDate DESC
> Example 2:
> Get the average of the previous 10 (or less) transactions for each
> individual transaction.
> SELECT t.Product, t.Amount, AVG(t2.Amount) AS HistoricalAverage
> FROM Transaction t
> INNER JOIN TOP 10 Transaction t2
> ON t.Product = t2.Product AND t2.TradeDate <= t.TradeDate
> ORDER BY t2.TradeDate DESC
> GROUP BY t.Product, t.Amount
> In order to get this to work... the INNER JOIN would need to support
> the TOP and the ORDER BY keywords... I don't believe it does... any
> thoughts on how to accomplish the results I'm looking for?
> Thanks!
> Brian Rice
>|||I'll rewrite the transaction a little and give some sample data.
SELECT t.Product, t.TradeDate, t.Amount, AVG(t2.Amount) AS
Previous3TransactionsAverage
FROM Transaction t
INNER JOIN TOP 3 Transaction t2
ON t.Product = t2.Product AND t2.TradeDate <= t.TradeDate
ORDER BY t2.TradeDate DESC
GROUP BY t.Product, t.Amount
Data:
Gold 1/1/2005 2
Gold 1/2/2005 3
Gold 1/3/2005 4
Gold 1/4/2005 5
Query Results:
Product Date Amount Previous3TransactionsAverage
Gold 1/2/2005 3 2
Gold 1/3/2005 4 2.5
Gold 1/4/2005 5 3|||Actually... here's a little extra data to fully illustrate:
Data:
Gold 1/1/2005 2
Gold 1/2/2005 3
Gold 1/3/2005 4
Gold 1/4/2005 5
Gold 1/5/2005 6
Query Results:
Product Date Amount Previous3TransactionsAverage
Gold 1/2/2005 3 2
Gold 1/3/2005 4 2.5
Gold 1/4/2005 5 3
Gold 1/5/2005 6 4|||IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP TABLE #Data
SET DATEFORMAT DMY
SELECT TOP 0 NULL AS Type , CAST( NULL AS DATETIME) AS [Date] , CAST(
NULL AS DECIMAL(23,1)) AS QTY
INTO #Data
UNION ALL SELECT 'Gold' , '1/1/2005' , 2
UNION ALL SELECT 'Gold' , '1/2/2005' , 3
UNION ALL SELECT 'Gold' , '1/3/2005' , 4
UNION ALL SELECT 'Gold' , '1/4/2005' , 5
UNION ALL SELECT 'Gold' , '1/5/2005' , 6
SELECT
Type
, [Date]
, QTY
, (
SELECT
AVG( QTY )
FROM
(
SELECT TOP 3
QTY
FROM
#Data DataSub
WHERE
Type = Data.Type
AND
[Date] < Data.[Date]
ORDER BY
[Date] DESC
) AVG_DataSub
) AS Prev3
FROM
#Data Data
ORDER BY
Data.Type
, Data.[Date] DESC
<mail@.brianrice.com> wrote in message
news:1130155419.589347.294900@.g49g2000cwa.googlegroups.com...
> Actually... here's a little extra data to fully illustrate:
> Data:
> Gold 1/1/2005 2
> Gold 1/2/2005 3
> Gold 1/3/2005 4
> Gold 1/4/2005 5
> Gold 1/5/2005 6
> Query Results:
> Product Date Amount Previous3TransactionsAverage
> Gold 1/2/2005 3 2
> Gold 1/3/2005 4 2.5
> Gold 1/4/2005 5 3
> Gold 1/5/2005 6 4
>|||ps, an Index ( TYPE , [Date] DESC ) would greatly improve the speed of this
query.
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:435ccd59$0$137$7b0f0fd3@.mistral.news.newnet.co.uk...
> IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP TABLE #Data
>
> SET DATEFORMAT DMY
> SELECT TOP 0 NULL AS Type , CAST( NULL AS DATETIME) AS [Date] , CAST(
> NULL AS DECIMAL(23,1)) AS QTY
> INTO #Data
> UNION ALL SELECT 'Gold' , '1/1/2005' , 2
> UNION ALL SELECT 'Gold' , '1/2/2005' , 3
> UNION ALL SELECT 'Gold' , '1/3/2005' , 4
> UNION ALL SELECT 'Gold' , '1/4/2005' , 5
> UNION ALL SELECT 'Gold' , '1/5/2005' , 6
>
> SELECT
> Type
> , [Date]
> , QTY
> , (
> SELECT
> AVG( QTY )
> FROM
> (
> SELECT TOP 3
> QTY
> FROM
> #Data DataSub
> WHERE
> Type = Data.Type
> AND
> [Date] < Data.[Date]
> ORDER BY
> [Date] DESC
> ) AVG_DataSub
> ) AS Prev3
> FROM
> #Data Data
> ORDER BY
> Data.Type
> , Data.[Date] DESC
>
>
> <mail@.brianrice.com> wrote in message
> news:1130155419.589347.294900@.g49g2000cwa.googlegroups.com...
>|||Rebecca... very ... it occurred to me that you could use a (select
top n...) as the source in an inner join... but the problem is that the
outer date and type is not available there. I didn't know that the
result of a select statement could be used in a column... though I
suppose that's what a user defined function is. Nice!
Now... is there a better way, in the following example, to do this (get
the MIN, MAX, *and* AVG)... or are we stuck with three separate
queries?
Thanks!
----
---
The following works though it sometimes gives me a deadlock error
(probably because of parallelism)... I could probably use the hint to
not run in parallel.
IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP TABLE #Data
SET DATEFORMAT DMY
SELECT TOP 0 NULL AS Type , CAST( NULL AS DATETIME) AS [Date] ,
CAST(
NULL AS DECIMAL(23,1)) AS QTY
INTO #Data
UNION ALL SELECT 'Gold' , '1/1/2005' , 2
UNION ALL SELECT 'Gold' , '1/2/2005' , 3
UNION ALL SELECT 'Gold' , '1/3/2005' , 4
UNION ALL SELECT 'Gold' , '1/4/2005' , 5
UNION ALL SELECT 'Gold' , '1/5/2005' , 6
--
SELECT Type, [Date], QTY,
(SELECT AVG(QTY)
FROM (SELECT TOP 3 QTY
FROM #Data ds
WHERE Type = pt.Type AND [Date] < pt.[Date]
ORDER BY [Date] DESC) dt) AS Avg3,
(SELECT MIN(QTY)
FROM (SELECT TOP 3 QTY
FROM #Data ds
WHERE Type = pt.Type AND [Date] < pt.[Date]
ORDER BY [Date] DESC) dt) AS Min3,
(SELECT MAX(QTY)
FROM (SELECT TOP 3 QTY
FROM #Data ds
WHERE Type = pt.Type AND [Date] < pt.[Date]
ORDER BY [Date] DESC) dt) AS Max3
FROM [#Data] pt
ORDER BY Type, [Date] DESC
----
---
The following give me "Only one expression can be specified in the
select list when the subquery is not introduced with EXISTS".
IF OBJECT_ID('tempdb..#Data') IS NOT NULL DROP TABLE #Data
SET DATEFORMAT DMY
SELECT TOP 0 NULL AS Type , CAST( NULL AS DATETIME) AS [Date] ,
CAST(
NULL AS DECIMAL(23,1)) AS QTY
INTO #Data
UNION ALL SELECT 'Gold' , '1/1/2005' , 2
UNION ALL SELECT 'Gold' , '1/2/2005' , 3
UNION ALL SELECT 'Gold' , '1/3/2005' , 4
UNION ALL SELECT 'Gold' , '1/4/2005' , 5
UNION ALL SELECT 'Gold' , '1/5/2005' , 6
--
SELECT Type, [Date], QTY,
(SELECT AVG(QTY) AS AvgQty, MIN(QTY) AS MinQty, MAX(QTY) AS
MaxQty
FROM (SELECT TOP 3 QTY
FROM #Data ds
WHERE Type = pt.Type AND [Date] < pt.[Date]
ORDER BY [Date] DESC) dt)
FROM [#Data] pt
ORDER BY Type, [Date] DESC