June 11, 2019 at 2:58 pm
I need the Max record (based on entered date) from two tables to compare results.
I made two table, each with a Row Number.
then, I run this......
select * from #tmpSTP P
left join #tmpZATS Z
on P.Tracker_PSYH = Z.ZATS_PSYH
and P.RowNum = 1
and Z.RowNum = 1
But i get row twos from P.
Is there something silly i don't see??
June 11, 2019 at 3:08 pm
The problem was you had P.RowNum=1 as part of the left join.
Try this instead:
select *
from #tmpSTP P
left join #tmpZATS Z
on P.Tracker_PSYH = Z.ZATS_PSYH
and Z.RowNum = 1
where P.RowNum = 1
June 11, 2019 at 3:09 pm
Is this a many-to-many join?
P.Tracker_PSYH = Z.ZATS_PSYH
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 11, 2019 at 3:15 pm
without seeing the table structure or the data it is returning we will struggle to help
have you thought about using Row_number() over (partition by … order by …) as rn
add this to your query and you can just add in "where rn=1"
It's not the exact code you need (I can't see your database or understand how the table structures relate) but that's how I normally get round that issue.
if you use a date field in the order by section and the 2 records have the same date then you might not get the record you want.. you may have to add another field to the order by section of the rownum function
MVDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply