I have a table of complaints. There's a category field and dollar amount field. I created a query to group by category and then calculate the 10th percentile dollar value for each category by using a subquery. Below is the SQL. It works but runs very slow when opening. There's only 1,000 total records so I'm thinking my SQL just isn't designed well.
Any suggestions on how to better optimize it?
Code:SELECT tblComplaints.ComplaintType, Max(tblComplaints.[DollarAmount]) AS 10thPercentileFROM tblComplaints WHERE (((tblComplaints.[DollarAmount]) In (SELECT TOP 10 PERCENT [DollarAmount] FROM tblComplaints AS sub WHERE sub.ComplaintType= tblComplaints.ComplaintType ORDER BY [DollarAmount] ASC ))) GROUP BY tblComplaints.ComplaintType;