CODEWITHSUNDEEP
sqlvbams-access
aaendewy
aaendewy

Reputation: 63

SELECT Top values for each records

I have been battling through this query/query design for sometime now and I thought it's time to ask the experts! Here's my table results:

ID   | Status       | date         |
---------------------------------
05   | Returned     | 20/6/2018    |
03   | Sent         | 12/5/2018    |
01   | Pending      | 07/6/2018    |
01   | Engaged      | 11/4/2018    |
03   | Contacted    | 16/4/2018    |
05   | Surveyed     | 04/3/2017    |
05   | No Contact   | 05/3/2017    |

How do I get it to return top/newest value for each ID:

ID   | Status       | date         |
---------------------------------
05   | Returned     | 20/6/2018    |
03   | Sent         | 12/5/2018    |
01   | Pending      | 07/6/2018    |

I've tried group by, TOP 1, Distinct and results still not what I wanted. Also, displaying the results by top 5% is won't do either as the ID can be more than just 3 types.

My QUERY below:

INSERT INTO TmpAllcomsEmployee ( StatusID, EmployeeID, CommunicationDate )
SELECT DISTINCT CommunicationLog.StatusID, TmpAllcomsEmployee.EmployeeID, 
Max(CommunicationLog.CommunicationDate) AS MaxOfCommunicationDate
FROM CommunicationLog RIGHT JOIN TmpAllcomsEmployee ON 
     CommunicationLog.EmployeeID = TmpAllcomsEmployee.EmployeeID
GROUP BY CommunicationLog.StatusID, TmpAllcomsEmployee.EmployeeID
ORDER BY Max(CommunicationLog.CommunicationDate) DESC;

Upvotes: 1

Views: 83

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

One method is a correlated subquery:

select cl.*
from CommunicationLog as cl
where cl.date = (select max(cl2.date)
                 from CommunicationLog as cl2
                 where cl2.EmployeeID = cl.EmployeeID
                );

This gets the most recent record for each employee in CommunicationLog. You can join in the other table if you really need it. It does not seem unnecessary unless you are using it for filtering.

Upvotes: 1

Related Questions