SQL query using Max not returning all records

[SR24]

I am wondering if anyone has any idea why the following query isn't returning the correct number of records. The query used is:

SELECT dbo.vw_Debtor.PacketId, dbo.vw_Debtor.FileKey
FROM dbo.vw_Debtor INNER JOIN
(
SELECT PacketId,MAX(FileKey) FileKey
FROM dbo.vw_Debtor
GROUP BY PacketId) A
ON dbo.vw_Debtor.PacketId=A.PacketId AND dbo.vw_Debtor.FileKey=A.FileKey

WHERE
dbo.vw_Debtor.ClientId = 'DOE001'

The query returns 58,877 records which is 2 short of the actual number that should be returned. I ran the nested query by itself and the 2 records that I am missing are included when it's run alone. But for some reason these 2 records do not show up when the entire query is run.

Anyone have any ideas?

Thanks!

 

[SQLBill]

Do the two missing records have dbo.vw_Debtor.ClientId = 'DOE001'?

If so, could they have the 'DOE001' mis-typed? For example, I'm guessing that is the letters DOE followed by the numbers 001....what if it's D zero E zero zero 1 or DOEO zero 1?

-SQLBill

Posting advice: FAQ481-4875

 

[vongrunt]

I guess ClientId is supposed to be always the same per PacketId, but isn't.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SR24]

Thanks for the tip. That appears to be the problem. The ClientID of DOE001 was not misspelled but it appears there are records with the same PacketId with a different ClientId that I was unaware of. If I move my WHERE clause into the nested query do you think this will solve my problem?

 

[vongrunt]

> If I move my WHERE clause into the nested query do you think this will solve my problem?

I'm not familiar with logic behind the query that is supposed to return 58,879 rows, so it is hard to compare something with unknown reference. Other than that, I'd say - yes.

Note that in that case query semantics changes a little bit. A.FileKey becomes greatest value per PacketId for which ClientId='DOE001', not greates value per PacketId that contains expected ClientId at least once.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)