Top values in a query

[George]

Dear friends,
I am dealing with a forest inventory database. I have the PlotNumber and
the Diameter of each tree found in the plot (many trees per plot).
I want to select from each plot the 20% of trees, having the biggest
diameter, e.g plot 1 with 20 trees to select 4 bulkiest trees, plot 2 with 40
trees to select the 8 bulkiest trees.
How can I do that? I tried the top values in a query but does not the thing
I want to do.
Thank you in advance
George

 

[John Spencer]

You would need to do a coordinated subquery.

The query would probably look something like the following.

SELECT PlotNumber, TreeID
FROM SomeTable
WHERE TreeID in

(SELECT TOP 20 PerCent TreeID
FROM SomeTable as Temp
WHERE Temp.PlotNumber = SomeTable.PlotNumber
ORDER BY Temp.Diameter DESC)

 

[Mike Gramelspacher]

Because I am trying to learn more SQL, I tried by hand too. A subquery to
get the rank by tree diameter and another subquery to get the count per
plot.

SELECT TP.tree_nbr, TP.plot_nbr, TP.tree_diameter, (SELECT Count(* ) + 1
FROM Treeplots AS TP1
WHERE TP1.plot_nbr = TP.plot_nbr
AND TP1.tree_diameter < TP.tree_diameter) 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(* ) + 1
FROM Treeplots AS TP1
WHERE TP1.plot_nbr = TP.plot_nbr
AND TP1.tree_diameter < TP.tree_diameter) >= (Select
Count(T.plot_nbr)
FROM Treeplots AS T WHERE TP.plot_nbr = T.plot_nbr)*.8;

 

[George]

Thanks a lot John for your reply,
I have modified a bit the given SQL as:

SELECT SomeTable.PlotNumber, SomeTable.Diameter
FROM SomeTable
WHERE (((SomeTable.Diameter) In (SELECT TOP 20 PerCent Diameter
FROM SomeTable as Temp
WHERE Temp.PlotNumber = SomeTable.PlotNumber
ORDER BY Temp.Diameter DESC)))
ORDER BY SomeTable.PlotNumber, SomeTable.Diameter DESC;

In my table (SomeTable) i have plotno 1 with 14 trees - gives me 3 trees -
correct
PlotNo 2 with 26 trees - gives me 7 trees - should give me 5
PlotNo 3 with 5 trees - gives me 1 tree - correct
PlotNo 4 with 41 trees - gives me 9 trees - should give me 8

What am I missing? Can you further help?

Ο χÏήστης "John Spencer" έγγÏαψε:

 

[John Spencer]

Top returns ties in the sort order. For example, look at plot 2 with 26
trees. If the diameter of the fifth tree is the same as the diameter of the
6th and 7th tree then you will get 7 trees returned. If all you want is the
plot number and the diameter of the trees, you could use the following which
might return only 1 diameter if the diameter for the top 20 percent was the
same for all the trees in that top 20 percent.


SELECT DISTINCT SomeTable.PlotNumber, SomeTable.Diameter
FROM SomeTable
WHERE (((SomeTable.Diameter) In (SELECT TOP 20 PerCent Diameter
FROM SomeTable as Temp
WHERE Temp.PlotNumber = SomeTable.PlotNumber
ORDER BY Temp.Diameter DESC)))
ORDER BY SomeTable.PlotNumber, SomeTable.Diameter DESC;

If you wish to eliminate ties, another method is to add to the order by
clause of the subquery. Assuming that each tree has a unique number or some
other field (or fields) that identify it uniquely, add the field(s) to the
ORDER BY clause.

I'm not sure how Access handles TOP n percent in terms of returning a number
of records if there are no ties. Twenty percent of 26 is 5.2. So, Access
should return 5 records plus two-tenths of a sixth record. That is
naturally not possible, so the designers had to make a choice. Access could
interpret a fractional number as a truncated integer (5), as rounded (5), or
it could round up to the next integer (6).