Solved

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

Posted on 2013-01-25
7
395 Views
Last Modified: 2013-01-29
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
0
Comment
Question by:CraigMan
  • 3
  • 2
  • 2
7 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38820995
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.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 38822998
Removed comment, not for this question.
0
 

Author Comment

by:CraigMan
ID: 38831204
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
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 38831450
Here you go...

SELECT t.client_id, t.user_name, t.update_date_utc
FROM tblClients AS t
WHERE (t.client_id &  t.update_date_utc) IN  
(
        SELECT TOP 2 cdupe.client_id & cdupe.update_date_utc 
        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


Try Query1 in the attached  DB
db2.mdb
0
 
LVL 30

Expert Comment

by:hnasr
ID: 38832067
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
0
 

Author Closing Comment

by:CraigMan
ID: 38832074
Just what I needed

Thanks
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38832135
Glad to help :)
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question