[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2013-01-25
7
Medium Priority
?
412 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 31

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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 61

Accepted Solution

by:
mbizup earned 2000 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 31

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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

591 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