Why is SELECT TOP 200 returning more rows than 200?

[Amy Badgett]

Here is my query:

SELECT TOP 200 tblCallLog.tblCallLog_callDate, (tblClients.tblClients_firstName & ' ' & tblClients.tblClients_lastName & ' ' & tblClients.tblClients_sfx) AS clientName, 
tblClients.tblClients_mailingAddress, (tblClients.tblClients_city & ', ' & tblClients.tblClients_state & ' ' & tblClients.tblClients_zip) AS addLine2, 
tblCallLog.tblCallLog_dvdTractRequested, tblCallLog.tblCallLog_callDate, 
tblCallLog.tblCallLog_dvdTractSent
FROM tblClients INNER JOIN tblCallLog ON tblClients.tblClients_clientID = tblCallLog.tblCallLog_clientID
WHERE (((tblClients.tblClients_mailingAddress) Is Not Null) 
AND ((tblCallLog.tblCallLog_dvdTractRequested)=True) AND ((tblCallLog.tblCallLog_dvdTractSent) Is Null))
ORDER BY tblCallLog.tblCallLog_callDate;
 

Is there any reason why this would return 369 rows instead of the 200 I am asking for?

[Rabbit]

Probably because of duplicate rows. Try adding a unique field that would be unique across the join.

[Amy Badgett]

The callDate, dvdTractRequested, and dvdTractSent are all unique to one table. It's still returning more than what I ask for.

[Rabbit]

You said it's unique to one table. It needs to be unique across tables.

[Amy Badgett]

okay, so now I selected clientID which is what the two tables are joined on, but it still is giving me 45 more than what I asked for... is that what you meant? a field that is the same on both tables?

[Rabbit]

Not quite, it will need to be two or more fields that when used together, will make every record unique.

[NeoPa]

Here's an illustration of the sort of issue that could cause more rows than stipulated appearing in a TOP N query (This is also explained in the SQL section of Help - See Finding Jet SQL Help).

Assuming a simple dataset of :

Field
  A
  B
  C
  C
  C
  D
  E

and SQL of :

SELECT TOP 3
         [Field]
FROM     [MyTable]
ORDER BY [Field]

The result set would be :

Field
  A
  B
  C
  C
  C

As the third item in the sort order is C, it only specifies which values to include, not how many records. There are no more than three separate values included in the output.

Don't assume it works on the basis of the number of separate items though. The following result set, when the TOP predicate is set to 4, illustrates this :

Field
  A
  B
  C
  C
  C

Notice the result sets are identical. Setting it to 6 would only add the value D. It will always show all records of the same value (within the sort order) whenever the nth value is duplicated.

[Amy Badgett]

So, "SELECT DISTINCT TOP 200" would help it seems?

[NeoPa]

Nothing you've said so far leads me to believe that's what you're after.

If you look again at post #7 you'll see that the results would be quite different.

It's possible that's actually what you're after, but it's not what you've asked for at any point.