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!
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!