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
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.