As far as I know, the Top predicate rounds UP to the next whole number when
using PerCent. I would guess that the decision was based on the logic that
you can't return part of a record, so return the entire record.
I know of no easy way do what you seem to want - round the calculated
percent down or round it to the nearest whole number. I would think it
could be done with a ranking query plus calculated query to identify records
for your current query.. Both would have to be based on your current query
using the order by clause and where clause in your current query. Of course
your current query would NOT use the TOP predicate
Here is something with which I came up with a good deal of trouble
attempting to solve a problem posed here on the 21th I think.
Query: Top ? percent from group
PARAMETERS [Enter percent as decimal:] Decimal;
SELECT TP.tree_nbr, TP.plot_nbr, TP.tree_diameter, (SELECT Count(* )
FROM Treeplots AS TP1
WHERE TP1.plot_nbr = TP.plot_nbr
AND TP1.tree_diameter < TP.tree_diameter
OR (TP.tree_diameter = TP1.tree_diameter)
AND (TP.tree_nbr <= TP1.tree_nbr)) AS size_rank, (SELECT
Count(T.plot_nbr)
FROM Treeplots AS T
WHERE TP.plot_nbr = T.plot_nbr) AS count_per_plot
FROM Treeplots AS TP
GROUP BY TP.tree_nbr, TP.plot_nbr, TP.tree_diameter
HAVING ((((SELECT Count(* )
FROM Treeplots AS TP1
WHERE TP1.plot_nbr = TP.plot_nbr
AND TP1.tree_diameter > TP.tree_diameter
OR (TP.tree_diameter = TP1.tree_diameter)
AND (TP.tree_nbr >= TP1.tree_nbr))/(SELECT
Count(T.plot_nbr)
FROM Treeplots AS T
WHERE TP.plot_nbr = T.plot_nbr))<=
[Enter percent as decimal:]));
Results using a parameter value of .2 (20 percent):
tree_nbr plot_nbr tree_diameter size_rank count_per_plot
14 1 23.1 12 12
27 3 32.5 11 11
Plot 2 only has 7 rows, so 10% of 7 is a fraction of a row. What is a
fraction of a row any how?
