JonMny
asked on
Sql Query alternate
I have this query (simplified). I have narrowed down the issue to the Amount column query. I know if I can move that column query to the bottom and join it will be faster. I have written the query doing so and I get the results that I need except it gives me extra rows. Can anyone suggest how this might be done.
Select
aID,
aID,
FG,
TheDate,
(
Select top 1
Amount
from
AccountHistory i
Where
i.TheDate >= '1/1/2011' and
aID Between '0' and '9' and
o.aID = i.aID and
o.aID = i.aID and
o.FG = i.FG and
o.TheDate >= i.TheDate
Order by
i.aID,
i.aID,
i.FG,
i.TheDate desc
) as Amount,
from
MainTable
Select
aID,
aID,
FG,
TheDate,
(
Select top 1
Amount
from
AccountHistory i
Where
i.TheDate >= '1/1/2011' and
aID Between '0' and '9' and
o.aID = i.aID and
o.aID = i.aID and
o.FG = i.FG and
o.TheDate >= i.TheDate
Order by
i.aID,
i.aID,
i.FG,
i.TheDate desc
) as Amount,
from
MainTable
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
need just the first record, that's why just joining is not working for me.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your comments I found an alternat solution.
If you are trying to pull a single record in the subquery you can use 'first' instead of
'top 1'. I believe that you may be pulling the top 1% instead of a single record.