Find Max() updated time for a group of users Database is MariaDB 10.3.25
I have 2 columns that are relevant:
UserID and LastUpdate
UserID consists of user@domain
LastUpdate is a date field.
Here is my issue - I have the current query:
select a.UserID, 
substring_index(a.UserID, '@', -1), 
max(a.lastupdate) 
from MyTable a 
group by a.UserID
having max(a.lastupdate) &lt; '2020-03-31' 

This shows all the Users that haven't updated in just over a year and the domain. However there is the following scenario that I want to account for:
UserID LastUpdate
A@A.com 2020-08-16
B@A.com 2019-05-16
C@A.com 2021-05-05
With the current query, B@A.com will be captured, and therefore domain A.com will be captured as not in use, but the user C@A.com is still active - I want to do a Max(a.lastupdate) but have it grouped by  substring_index(a.UserID, '@', -1)
but also spit out all the users for that domain.
I'm sure the answer is starring me in the face...
See if its what you want
-- this main query retrieve all users under the subquery domain
SELECT a.UserID
    , substring_index(a.UserID, '@', -1)
    , MAX(a.lastupdate)
FROM MyTable a
WHERE substring_index(a.UserID, '@', -1) IN (

    -- this subquery search all domain inactive in last 12 months
    SELECT substring_index(a.UserID, '@', -1) AS domain
    FROM MyTable a
    GROUP BY substring_index(a.UserID, '@', -1)
    HAVING MAX(a.lastupdate) &lt; '2020-03-31'

)
ORDER BY substring_index(a.UserID, '@', -1), a.lastupdate