Create a Top N Values per Group Query

[cgemmill1]

I am looking for some assistance in desinging a query that returns the top 4 records for each group. I have a base query that includes HRET_OrganizationID, HRET_MEasureID, Start Date, Numerator, Denominator, and Organization Name. This query serves to pull out the records I am interested in from the larger database.

The query I wrote to pull the top 4 is a select query with the following: HRET_OrganizationID (Ascending), HRET_MEasureID, Start Date (Descending), Numerator, Denominator. Under critieria for Start date I have the following (Select Top 4 [Start Date] from tbl_CDS where [HRET_OrganizationID]=[test21].[HRET_OrganizationID] Order by [Start date] desc)

The result I get is the Top 4 organizations. What I am looking for are the top four months for each group (organization).

Can anyone assist me?

 

[pbaldy]

See if this helps:

http://support.microsoft.com/kb/153747/en-us

 

[cgemmill1]

Paul, thank you for the response. I have reviewed this article and I am still unable to get the results I am looking for. I even tried not using a query but the actual tables and no luck.

 

[pbaldy]

Is that an underscore after the WHERE?

 

[cgemmill1]

Yes there was and after removing the underscore I was able to get the example to work in the Northwinds database. Using the same logic I applied the fields from my database. The end result is I keep getting asked to enter a value for HRET_OrganizationID

 

[pbaldy]

The parameter prompt typically means something is spelled wrong. Does the field perhaps have a space rather than an underscore?