Top values

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

0
Utf
7/25/2007 8:42:01 AM
access 16762 articles. 3 followers. Follow

4 Replies
630 Views

Similar Articles

[PageSpeed] 15

hi Geroge,

George wrote:
> 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 <--
0
Stefan
7/25/2007 9:01:12 AM
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" έγγραψε:

> Look at this link on "Subquery basics" especialy on the section of  "TOP n 
> records per group"
> 
> http://allenbrowne.com/subquery-01.html
> 
> 
> -- 
> Good Luck
> BS"D
> 
> 
> "George" wrote:
> 
> > 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
> > 
0
Utf
7/26/2007 5:00:02 AM
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


-- 
Good Luck
BS"D


"George" wrote:

> 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" έγγραψε:
> 
> > Look at this link on "Subquery basics" especialy on the section of  "TOP n 
> > records per group"
> > 
> > http://allenbrowne.com/subquery-01.html
> > 
> > 
> > -- 
> > Good Luck
> > BS"D
> > 
> > 
> > "George" wrote:
> > 
> > > 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
> > > 
0
Utf
7/26/2007 10:04:01 AM
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" έγγραψε:

> 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
> 
> 
> -- 
> Good Luck
> BS"D
> 
> 
> "George" wrote:
> 
> > 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" έγγραψε:
> > 
> > > Look at this link on "Subquery basics" especialy on the section of  "TOP n 
> > > records per group"
> > > 
> > > http://allenbrowne.com/subquery-01.html
> > > 
> > > 
> > > -- 
> > > Good Luck
> > > BS"D
> > > 
> > > 
> > > "George" wrote:
> > > 
> > > > 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
> > > > 
0
Utf
7/27/2007 4:44:02 AM
Reply:

Similar Artilces: