In the problem tables, are there no fields that might help you identify the current owner? A date field would help.
There is a column "OWNSSNEXT" (Owner SSN Extension) that is an incrementing alpha. Original Owner is "A" fourth owner is "D". So I guess Max(OWNSSNEXT) is the current Owner.
1 person found this helpful
You put that max(ownsnext) in a subquery.
from table t join
(select field, max(ownsnext) theowner
group by field) sq on t.field = sq.field and t.ownsnext= theowner
You have to give your subquery an alias when you do this.
I have a sloution. Thanks Dan.
SELECT TOP 3 D.OBN, Sum(R.Amount) AS SumOfAmountWon, D.HName
FROM (Racestck F INNER JOIN Races R ON F.OBN = R.OBN)
INNER JOIN (SELECT DISTINCT OBN, HName FROM DamSire
) D ON F.DamOBN = D.OBN
GROUP BY D.OBN, D.HName
ORDER BY 2 DESC