Let us say I've two tables during my database.

```
TABLE:Categories
ID|CategoryName
01|CategoryA
02|CategoryB
03|CategoryC
```

along with a table that references the Groups and has a column storing some random number.

```
TABLE:CategoriesAndNumbers
CategoryType|Number
CategoryA|24
CategoryA|22
CategoryC|105
.....(20,000 records)
CategoryB|3
```

Now, how do you remove this data? So, I wish to understand what the three littlest amounts are from each category and remove the relaxation. The finish result could be such as this:

```
TABLE:CategoriesAndNumbers
CategoryType|Number
CategoryA|2
CategoryA|5
CategoryA|18
CategoryB|3
CategoryB|500
CategoryB|1601
CategoryC|1
CategoryC|4
CategoryC|62
```

At this time, I'm able to obtain the littlest amounts between all of the groups, but I'd like each category to become in comparison individually.

EDIT: I am using Access and here's my code to date

```
SELECT TOP 10 cdt1.sourceCounty, cdt1.destCounty, cdt1.distMiles
FROM countyDistanceTable as cdt1, countyTable
WHERE cdt1.sourceCounty = countyTable.countyID
ORDER BY cdt1.sourceCounty, cdt1.distMiles, cdt1.destCounty
```

**EDIT2: Because of Remou, here will be the working query that solved my problem. Thanks!**

```
DELETE
FROM CategoriesAndNumbers a
WHERE a.Number NOT IN (
SELECT Top 3 [Number]
FROM CategoriesAndNumbers b
WHERE b.CategoryType=a.CategoryType
ORDER BY [Number])
```

You could utilize something similar to:

```
SELECT a.CategoryType, a.Number
FROM CategoriesAndNumbers a
WHERE a.Number IN (
SELECT Top 3 [Number]
FROM CategoriesAndNumbers b
WHERE b.CategoryType=a.CategoryType
ORDER BY [Number])
ORDER BY a.CategoryType
```

The problem with this particular is the fact that Jet/ACE Top chooses duplicate values where they exist, so you won't always get three values, but more, if you will find ties. The issue can frequently be solved having a key area, if a person is available :

```
WHERE a.Number IN (
SELECT Top 3 [Number]
FROM CategoriesAndNumbers b
WHERE b.CategoryType=a.CategoryType
ORDER BY [Number], [KeyField])
```

However, I don't think it can help in cases like this, since the outer table includes ties.

Order it by number and take 3, discover exactly what the greatest number is after which remove rows where `Number`

is more than the `Number`

.

I imagine it will have to be two seperate queries as the business tier would contain the value for that greatest number from the 3 results and dynamically build the query to remove the relaxation.