Get top 10 results for each group for each month (1 Viewer)

[Db-why-not]

How do I get the top ten results of records from each group (person) for each month. I have a query that I am using that sums up several fields. I only want 10 records to be summed every month for every person.

I am pulling the query over to excel and I was creating a pivot table that was filtering people records and summing up there scores for each question. I am also filtering by month. I only want to add the top 10 records from each person for each month.

 

[theDBguy]

Hi. Have you tried using a subquery. Try searching for "Top N Per Group."

 

[ebs17]

SELECT
   T.*
FROM
   TabX AS T
WHERE
   T.ID IN
      (
         SELECT TOP 10
            ID
         FROM
            TabX AS X
         WHERE
            X.Person = T.Person
               AND
            Year(X.AnyDate) * 100 + Month(X.AnyDate) = Year(T.AnyDate) * 100 + Month(T.AnyDate)
         ORDER BY
            X.AnyValue DESC
      )

 

[Db-why-not]

SELECT
   T.*
FROM
   TabX AS T
WHERE
   T.ID IN
      (
         SELECT TOP 10
            ID
         FROM
            TabX AS X
         WHERE
            X.Person = T.Person
               AND
            Year(X.AnyDate) * 100 + Month(X.AnyDate) = Year(T.AnyDate) * 100 + Month(T.AnyDate)
         ORDER BY
            X.AnyValue DESC
      )

Any tips on how to set this up so I can link the query to excel pivot table. I did have it linked to excel pivot table.

I want to group field MarkedCompleteBy ( This is a list of names), then group by field: tblMA_workload.dtfixed, (Months). I want to show only to records for each person that is in the MarkedCompleteBy field for each month.
I am linking the query to excel and I was using a pivot table to group the MArkedCompleteBy and filter by the month tblMA_workload.dtfixed, then
Each of these fieldsblMA_Audit.bIENsSamePT, tblMA_Audit.bIENSSameCHCS, tblMA_Audit.bMergeNotIncluded, tblMA_Audit.bQueueWorksheetComplete, tblMA_Audit.bErrorsMarked, tblMA_Audit.bErrorsFixed, tblMA_Audit.bUpdatedSurvivingIEN, tblMA_Audit.bIdentifiersRemoved, tblMA_Audit.bAliasUpdated, tblMA_Audit.bMergeConfirmationRecord) I was summing up the records ( the values in those fields are equal to 1 or 0)
[%Accuracy] field I was averaging that value in my pivot table

I have a timeline slicer that would filter by the month in my pivot table base off of field: tblMA_workload.dtfixed

For each month some people in the MArkedCompleteBy had more than 10 records Audited or less. I only want to summarize 10 records for each month. In the 2nd column of my pivot table I did a count of MarkedCompleteBy to show the number of records each person had. I want that column to only be 10.
If a person had 10 records for a month and they got 100% then every value added would be 1 so each colum would add up to 10 for that person. I only want it to count /show 10 records for each month, so show the top 10 records.

SELECT tblMA_Audit.lPersonID, tblMA_workload.Name AS MarkedCompleteBy, tblMA_workload.dtfixed, tblMA_Audit.bIENsSamePT, tblMA_Audit.bIENSSameCHCS, tblMA_Audit.bMergeNotIncluded, tblMA_Audit.bQueueWorksheetComplete, tblMA_Audit.bErrorsMarked, tblMA_Audit.bErrorsFixed, tblMA_Audit.bUpdatedSurvivingIEN, tblMA_Audit.bIdentifiersRemoved, tblMA_Audit.bAliasUpdated, tblMA_Audit.bMergeConfirmationRecord, ([bIENsSamePT]+[bIENSSameCHCS]+[bMergeNotIncluded]+[bQueueWorksheetComplete]+[bErrorsMarked]+[bErrorsFixed]+[bUpdatedSurvivingIEN]+[bIdentifiersRemoved]+[bAliasUpdated]+[bMergeConfirmationRecord])\(10) AS [%Accuracy]
FROM tblMA_workload INNER JOIN tblMA_Audit ON tblMA_workload.lPersonID = tblMA_Audit.lPersonID
ORDER BY tblMA_Audit.lPersonID DESC;

 

[Db-why-not]

So I created a subquery, then I created another query to query my subquery and this is what I have. I think it works, I only have data for 1 month right now, so I cant check it to see if it works for other months. Then I linked it to excel as a pivot table and filter by month.

SELECT *
FROM subquery_ma
Where subquery_ma.lPersonID IN
(Select Top 10 lPersonID
From Subquery_MA As MA
Where MA.MarkedCompleteBy = subquery_ma.MarkedCompleteBy
ORDER BY MA. Dtfixed Desc, MA. lPersonID)
ORDER BY subquery_ma.lPersonID, subquery_ma.MarkedCompleteBy, subquery_ma.dtfixed;

 

[theDBguy]

So I created a subquery, then I created another query to query my subquery and this is what I have. I think it works, I only have data for 1 month right now, so I cant check it to see if it works for other months. Then I linked it to excel as a pivot table and filter by month.

SELECT *
FROM subquery_ma
Where subquery_ma.lPersonID IN
(Select Top 10 lPersonID
From Subquery_MA As MA
Where MA.MarkedCompleteBy = subquery_ma.MarkedCompleteBy
ORDER BY MA. Dtfixed Desc, MA. lPersonID)
ORDER BY subquery_ma.lPersonID, subquery_ma.MarkedCompleteBy, subquery_ma.dtfixed;

Hi. Glad to hear you got it sorted out. Good luck!

 

[Db-why-not]

I did the same thing for another report but instead I wanted top 25 records per person per month. This query keeps causing access to close/crash every time I try to open the query and view the data. It lets me look at the SQL code and the design view of the query but not at the data. I tried linking the query to excel and it causes the excel file to just close out also. I did a compact and repair but it didnt fix it.

SELECT *
FROM subquery_Q1
WHERE subquery_q1.lPersonID_Q1 IN
(Select Top 25 lPersonID_Q1
From Subquery_Q1 As Q1
Where Q1.MarkedCompleteBy = subquery_q1.MarkedCompleteBy
ORDER BY Q1.RecordAccessDate Desc, Q1.lPersonID_Q1)
ORDER BY subquery_q1.lPersonID_q1, subquery_q1.MarkedCompleteBy, subquery_q1.RecordAccessDate;

 

[ebs17]

This query keeps causing access to close/crash

Purple smoke with yellow flashes?

I tend to think that the required runtime for the query is extremely long, it looks like an inactivity to the outside world. Amount of data is work, a large amount of data is a lot of work and takes time accordingly. You have to acknowledge that first. So the first question arises as to the data record numbers involved.

Second, the subquery correlates with the top level of the main query. This means that the subquery must be executed not just once, but multiple times, in the worst case as often as the upper level has records. A lot of repetitions mean a multiplication of the effort, thus again running time. The correlating subquery is due to the task and can hardly be avoided. Sometimes, however, you can rephrase the entire task and then come to different and more efficient solutions.

Third: Subquery_Q1 is used as the data source. If the name doesn't lie, that's a query with its own processing effort. This can be tiny, but also huge in itself. A selection query does not contain any data, only the definition of which data is to be fetched from your data sources and how it is to be processed. If such a query is called or used, it must be executed again yourself.

Fourth: Indexes on table fields can in some cases significantly improve access and processing speed. To do this, however, one must have indices in the corresponding table fields, the query formulation must also allow the use of these indices, and ultimately the query optimizer must also be able to use it within the framework of the jet show schedule.

So if you want to consider all of the points mentioned in the sense of a good running time of the query, this is more than putting together a "working" query itself. Each point mentioned should be analyzed more closely.