Top 75 Displays 80

[Guest]

I have a query...

SELECT TOP 75 QryPerformAmount.Perform, QryPerformAmount.SongTitle,
TblClick.Tempo, TblClick.Rhythm
FROM TblClick RIGHT JOIN QryPerformAmount ON TblClick.SongTitle =
QryPerformAmount.SongTitle
ORDER BY QryPerformAmount.Perform DESC;

And if I specify top 25 it displays 25. If I specify top 50 it displays 50.
If I specify top 75 it displays 80. If I specify top 100 it displays 107.
If I specify top 200 it displays 296. There are a total of 296 records
displayed if no top is specified.
What's up with that?
JJ

 

[Guest]

Could the amounts be the same?
i.e. if you say top 3 amount and you have 8 records with:
99993333
it would show 4

just a guess

Dorian

 

[John Spencer]

Top displays Ties for the last item based on the field you are sorting by.
If you want to limit it to the exact number you specify, then add more
fields to the ORDER BY clause. I would recommend the primary keys from
tblClick and qryPerformAmount.or you can sort by all the fields in the
select clause to see if that will give you the desired result.


SELECT TOP 75 QryPerformAmount.Perform, QryPerformAmount.SongTitle,
TblClick.Tempo, TblClick.Rhythm
FROM TblClick RIGHT JOIN QryPerformAmount
ON TblClick.SongTitle = QryPerformAmount.SongTitle
ORDER BY QryPerformAmount.Perform DESC
, QryPerformAmount.SongTitle
, TblClick.Tempo
, TblClick.Rhythm

 

[Smartin]

I have a query...

SELECT TOP 75 QryPerformAmount.Perform, QryPerformAmount.SongTitle,
TblClick.Tempo, TblClick.Rhythm
FROM TblClick RIGHT JOIN QryPerformAmount ON TblClick.SongTitle =
QryPerformAmount.SongTitle
ORDER BY QryPerformAmount.Perform DESC;

And if I specify top 25 it displays 25. If I specify top 50 it displays 50.
If I specify top 75 it displays 80. If I specify top 100 it displays 107.
If I specify top 200 it displays 296. There are a total of 296 records
displayed if no top is specified.
What's up with that?
JJ

Recently burned by that issue myself, someone wiser than me quoted the
Help file on "TOP", paraphrased below:

The TOP predicate does not choose between equal values. In [your]
example, if the [75th through 80th elements] are the same, the query
will return [80] records.

 

[Guest]

Yeah, that's exactly what happened. I realized it upon further inspection,
but couldn't find my topic again.
I ended up making that query (without top), then another query to query that
query (without times performed), then another query to query the query that
queries the query (with top), then a report to sort the query alphebetically
and print. It is a ways around but it works. I'll try some of your code and
see if it cleans things up.
JJ

 

[Guest]

This worked perfectly.
Thanks, guys. You removed 2 extra queries.
Hehehe...
Hahaha...
Hohoho...
JJ