Solved

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

Posted on 2013-01-25
7
400 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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 …

751 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