Top 10 records for each ID

  • Thread starter Thread starter Thorson
  • Start date Start date
T

Thorson

I have a query with 1 table. I would like to query the top 10 records for
each indvidual ID. I have ~114 IDs with each having 100-200 Tempature
readings. I tried setting the properties of my query to top 10 but that
returned the top 10 values for the entire table not the top 10 for each ID.

Any suggestions?
 
Thorson said:
I have a query with 1 table. I would like to query the top 10 records for
each indvidual ID. I have ~114 IDs with each having 100-200 Tempature
readings. I tried setting the properties of my query to top 10 but that
returned the top 10 values for the entire table not the top 10 for each ID.
Click to expand...


You need to use a subquery to do that. I think this is one
way you could do it:

SELECT T.*
FROM tempuraturetable As T
WHERE T.Tempurature IN (SELECT TOP 10 X.Tempurature
FROM tempuraturetable As X
WHERE X.ID = T.ID
ORDER BY X.Tempurature)
 
Thanks, I haven't had time to try it out yet, I will try it out tomorrow/this
weekend and see how it works.
 
I am having a little trouble trying to figure out where my field names etc.
should substitute into what you suggested...

My table name is tblFescue
My I would like to select the top 10 values from the field "Temperature" for
each Animal ID (which is field "ID")

Both of these fields are from the same table (tblFescue)
 
Thorson said:
I am having a little trouble trying to figure out where my field names etc.
should substitute into what you suggested...

My table name is tblFescue
My I would like to select the top 10 values from the field "Temperature" for
each Animal ID (which is field "ID")

Both of these fields are from the same table (tblFescue)
Click to expand...


SELECT T.[Animal ID], T.Temperature
FROM tblFescue As T
WHERE T.Temperature IN (SELECT TOP 10 X.Temperature
FROM tblFescue As X
WHERE X.[Animal ID] = T.[Animal ID]
ORDER BY X.Temperature)
 
Back
Top