July 23, 2019 at 1:52 pm
T has 57,000 distinct rows
Z has over a million. I did row over partition on the key field (Z.zpsh)
When i run the following SQL, I only get 47,000 rows back.
Shouldn't I get 57,000? thanks
select * from #temp1 T
left join #temp2 Z
on T.entry = Z.zpsh
where z.rownumber = 1
July 23, 2019 at 2:19 pm
What do you mean by this?
T has 57,000 distinct rows
I mean, why did you use the word 'distinct'?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 23, 2019 at 2:23 pm
By including Z in the where clause you've effectively turned it into an inner join.
July 23, 2019 at 2:25 pm
No, your WHERE clause is likely filtering the rows.
Without the actual SQL statement, and some sample data, that's an educated guess.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 23, 2019 at 2:45 pm
I used "distinct" to make it clear that I had no duplicate values.
July 23, 2019 at 2:54 pm
For an OUTER JOIN, you must put conditions on the possibly-missing table in the JOIN clause, not in the WHERE clause.
select * from #temp1 T
left join #temp2 Z
on T.entry = Z.zpsh
and z.rownumber = 1
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 23, 2019 at 3:30 pm
That was it!!! I actually printed your reply and pinned it to my cube!!!!!!!!!!
Thanks so much
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply