Retrieve first 5 records within each group

[Jackie]

We want identify only the 5 highest field values within each group. The table contains multiple groups. The table is ordered by group and field value descending order. We want the first 5 records for each group.

 

[MichaelRed]

AFAIK, you will need to extract the top N for each grou and then concatenate them.

To do so is tedious, not difficult:

One query to Get the 'Unique' set of groups.

Several alternatives. I will describe ONE which is (relatively) easy to put into words:

Generate an Empty table [tblResultsSet] of the desired results (off-line activity).

Generate a query to 'empty' the table [tblResultsSet] as in:

[tab]Delete * from [tblResultsSet]

Generate a PARAMETER query where the PARAMETER(s) match the Grpoup definition. It should be an append query with [tblResultsSet] as the target/destiniation and the original source table as the source. Include the predicate Top N (for your situation N[/] = 5)

In a (brief) module, set your db and rst references and a qdf reference to the append query, execute the delete query, instantiate the rst (recordset) for the Groups.

In a Loop, instantiate the querydefinition (the QDF), set the parameter(s) from the group recordset and execute the append query.

When the loop is finished, [tblResultsSet] should hold the recordset you want.

Of course, it will not be quite that easy, as some several somethings will not be understood and/or go awry in the implementation details. On the other hand, you at least have a vague plan of attack which you may persue.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[CosmoKramer]

Check out the following link on Microsoft's Knowledge base:

http://support.microsoft.com/suppor... group&rnk=2&src=DHCS_MSPSS_gn_SRCH&SPR=ACC97

 

[tlbroadbent]

A simple SQL solution.

SELECT t.Group, t.GrpValue
FROM Table AS t
WHERE t.GrpValue In (SELECT Top 5 GrpValue FROM Table WHERE Group=t.Group ORDER BY GrpValue Desc)
ORDER BY t.Group, t.GrpValue Desc; Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.