Select only the last 10 occurance in a query of each Component No

[jon]

In a query I need to show only the last 10 of each component No in a
selected field.
The field has different component No:
Components have occurrences from 1 upwards.
I only want to see the last 10 of each component No in the field (or less
where less jobs have been made)
There is another date field in the table, and the table is grouped by
component No the sorted by date so I just need the last 10 of each component
No:

Advise please.

Thanks


Jon

 

[Guest]

Try this --
SELECT T.Product, T.YourDate, T.[component No], (SELECT COUNT(*)
FROM [jon] T1
WHERE T1.Product = T.Product
AND T1.[component No] >= T.[component No]) AS Rank
FROM jon AS T
WHERE ((((SELECT COUNT(*)
FROM [jon] T1
WHERE T1.Product = T.Product
AND T1.[component No] >= T.[component No]))<=10))
ORDER BY T.Product, T.[component No] DESC;