Top values

[Guest]

Dear friends, need your help again please.

I have a table: tbl_Plots (PlotID is the primar key - number byte) and
tbl_Data (ID is the primary key - autonumber, PlotID related to tblePlots,
height - number byte and diameter - number integer).

I need to select the 20 bulkiest trees of each plot, i.e. having the biggest
diameter. Also, perhaps in a plot less than 20 trees will be present so I
will need all of them.

Any suggestions?

Thanking you in advance,

GeorgeCY

 

[Stefan Hoffmann]

hi Geroge,

I have a table: tbl_Plots (PlotID is the primar key - number byte) and
tbl_Data (ID is the primary key - autonumber, PlotID related to tblePlots,
height - number byte and diameter - number integer).

I need to select the 20 bulkiest trees of each plot, i.e. having the biggest
diameter. Also, perhaps in a plot less than 20 trees will be present so I
will need all of them.

I don't understand what you mean with "tree", but you can use this query
as a start:

SELECT TOP 20 ID, PlotID, diameter
FROM tbl_Data
ORDER BY diameter DESC


mfG
--> stefan <--

 

[Guest]

Look at this link on "Subquery basics" especialy on the section of "TOP n
records per group"

http://allenbrowne.com/subquery-01.html

 

[Guest]

Dear friends, thanks a lot for the answers.

Dear Ofer,

I have used and modified the proposed code without any success:

SELECT query2.PlotÎumber, query2.DiameterWithTape2001, query2.RawID
FROM query2
WHERE (((query2.RawID) In (select top 3 RawID from query2 As Dupe where
Dupe.RawID = query2.RawID order by dupe.PlotNumber, Dupe.DiameterWithTape2001
DESC)))
ORDER BY query2.PlotÎumber, query2.DiameterWithTape2001 DESC;

As you understand I have created a query2 showing fields from the table
containing raw data from plots (trees) - is a forest inventory database.
Please note that RawID is a PK (autonumber) and the PlotNumber (number
integer) is the foreign key related to my main table.

Any suggestions please?

Ο χÏήστης "Ofer Cohen" έγγÏαψε:

 

[Guest]

If the RawID is PK, then it will always return 1 value

Q2.RawID = Q1.RawID

You need to make it equal to the group that within that group you want three
records

 

[Guest]

Thanks a lot Ofer for your kind respond,

I would appreciate much if you could explain me in more details (sorry I am
new to sql).

GeorgeCY

Ο χÏήστης "Ofer Cohen" έγγÏαψε: