Ranking grouped entries (or returning top values for a group)

Joined
Aug 16, 2006
Messages
1
Reaction score
0
This is killing me, and I know it's not that difficult.
I have a table called tmp_Order_Lines (an order history file).
This contains all of the order lines for all orders placed by all clients for all products.
Two fields of interest are ID_Code (original, huh?) and Enterdt.
ID_Code identifies a client's customer number and the product ordered.
Enterdt is the Date when an order was placed.

Here is an example:
ID_Code Enterdt
101_abc 08/01/2006
101_abc 07/01/2006
101_abc 06/01/2006
101_abc 05/01/2006
101_xyz 07/15/2006
101_xyz 06/15/2006
101_xyz 05/15/2006
102_abc 08/01/2006
102_abc 06/15/2006

Now that I've thoroughly bored you with my simple examples, here is my quandry:
I need to return the top 2 results by Enterdt for each ID_Code (grouped by ID_Code and sorted DESC by Enterdt).

Obviously, the top keyword function is Access isn't going to do me much good on its own.
I am trouble making the proper query return the top 2 values for each group.

I even looked at trying to rank them in descending order by Enterdt (Grouping by ID_Code) and pulling the first 2 ranked results, but had trouble with that.

I would actually prefer to rank them and return only the first two results, because we would have more versatility in letting a user modify the number of results he/she wants to see.

If anyone can help, I certainly would appreciate it.
If you need any further details, please let me know.

Thanks,

Randy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top