T-SQL SELECT TOP returns duplicates?

I would assume that there is some non determinism going on which means that different access methods can return different results Looking at the view definition the only place that appears likely would be if vwEntity_Address has some duplicates for Entity_ID This would make the top 1 Address_ID returned arbitrary in that case which will effect the result of the union operation when it removes duplicates Definitely this does look extremely suspect SELECT TOP 1 Address_ID FROM dbo. VwEntity_Address vea WHERE vea. Entity_ID = ets.

Entity_ID ORDER by ea. Address_Type_ID ASC, ea. Address_ID DESC You are ordering by values from the outer query in the cross apply.

This will have absolutely no effect whatsoever as these will be constant for a particular CROSS APPLY invocation Can you try changing to SELECT TOP 1 Address_ID FROM dbo. VwEntity_Address vea WHERE vea. Entity_ID = ets.

Entity_ID ORDER by vea. Address_ID DESC.

I would assume that there is some non determinism going on which means that different access methods can return different results. Looking at the view definition the only place that appears likely would be if vwEntity_Address has some duplicates for Entity_ID. This would make the top 1 Address_ID returned arbitrary in that case which will effect the result of the union operation when it removes duplicates.

Definitely this does look extremely suspect SELECT TOP 1 Address_ID FROM dbo. VwEntity_Address vea WHERE vea. Entity_ID = ets.

Entity_ID ORDER by ea. Address_Type_ID ASC, ea. Address_ID DESC You are ordering by values from the outer query in the cross apply.

This will have absolutely no effect whatsoever as these will be constant for a particular CROSS APPLY invocation. Can you try changing to SELECT TOP 1 Address_ID FROM dbo. VwEntity_Address vea WHERE vea.

Entity_ID = ets. Entity_ID ORDER by vea. Address_ID DESC.

Do you think using a Window functions to get the arbitrary 1st address make it more deterministic, or would it be the same as using CROSS APPLY – Conrad Frix Oct 5 at 16:39 @ConradFrix - To make it deterministic the OP would need to order by a tie breaker such as adding Address_ID. Into the end of the ORDER BY list. – Martin Smith Oct 5 at 16:41 hmm well ea.

Address_ID DESC in the CROSS APPLY already. Where else would the OP add Address_id? – Conrad Frix Oct 5 at 17:02 @Conrad - They are ordering by ea.

Address_Id not vea. Address_ID. The whole ORDER BY doesn't really make sense.

– Martin Smith Oct 5 at 17:06 OH that explains my confusion on why it was arbitrary. I had missed that on every reading of the sql – Conrad Frix Oct 5 at 17:08.

I was wondering if your view included a function, until I got to the end, where you say you use cross-apply. I would assume that is your problem, if your interested in the details, take a look at the various query plans. EDIT: Expansion of answer I.e.

Your function is non-deterministic and can either return more than one row per input or return the same row for different input. In combination, this means that you'll get exactly what you are seeing: duplicate rows under some circumsntaces. Adding a distinct to your view is the costly way to solve your problem, a better way would be to change your function so that for any input there is only one row output, and for a row output only one input will produce that row.

EDIT: I didn't see that you're now including your view definition. Your problem is definitely the cross apply, in particular you are sorting inside the cross apply by values from OUTSIDE of the cross apply, making the top 1 effectively random.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.