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'?
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
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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