asked on

Access Query to Select the 2 Most Recent Records of Each ClientID.

Good afternoon Experts,

I need a query that will select the 2 most recent records for each Client_ID, based on the date in the update_date_utc field. There may be 1, 2, 3, or more instances of each Client_ID. If only 1 instance exists, I don't want it selected. If 2 instances exist, I want both instances selected, and if 3 or more instances exist, I want the 2 most recent instances based on the date in the update_date_utc field. See attached spreadsheet.

Thanks in advance for your help.
Craig
ChangeOwner.xls

mbizup

Try this:

SELECT t.client_id, t.user_name, t.update_date_utc
FROM tblClients t
WHERE t.client_id IN (
        SELECT TOP 2 cdupe.client_id 
        FROM tblClients cdupe 
        WHERE cdupe.client_id  = t.client_id  AND  cdupe.client_id  IN 
               (SELECT c.client_id
                FROM tblClients c
                GROUP BY c.client_id
                HAVING Count(c.user_name) > 1) 
        ORDER BY cdupe.update_date_utc DESC
)

Open in new window

And take a look at the following article under the heading "top N records per group":
http://allenbrowne.com/subquery-01.html

This is a variation on that, with the HAVING clause limiting the output to groups of two or more.

You may have to replace occurrences of tblClients with your own table name.

Hamed Nasr

Removed comment, not for this question.

CraigMan

ASKER

mbixup,

Your solution did eliminate the single instances of client_id, but did not limit the result to the 2 most current records per client_id, if more than 2 instances exist. How do I limit output to 2 most current records?

Craig

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hamed Nasr

Try this:
For table a(aid, f1, f2) - aid is the primary key.
aID      f1      f2
1      1      1
2      1      2
3      1      3
4      2      2
5      4      3
6      4      5

Required output:
aid      f1      f2 - recent as higher f2 means more recent
2      1      2
1      1      1
6      4      5
5      4      3

SELECT a.aid, a.f1, a.f2
FROM a
WHERE ((((select count(x.f1) from a x where a.f1=x.f1))>1) AND (((select count(x.f1) from a x where a.f1=x.f1 and a.aid>=x.aid))<3))
ORDER BY a.f1, a.f2 DESC;

Open in new window

Output:
aid      f1      f2
2      1      2
1      1      1
6      4      5
5      4      3

CraigMan

ASKER

Just what I needed

Thanks

mbizup

Glad to help :)