Get max date for user from another table

Question

I have two tables, in one table I am storing user statuses and in the second logs.

"status" table

 id , customerId, userName, serviceId, status

"logs" table

id, customerId, logDate, status

I need to get the latest log for each customer for specific date intervals (from 2020-10-01 to 2020-11-31) and specific status (status = 6). All customer logs are stored in the "logs" table.

This is what I tried but no luck:

Select distinct (a.customerId),  a.userName, a.serviceId, a.status, max(logDate)
FROM status a 
JOIN logs b 
WHERE logDate BETWEEN '2020-10-01' AND '2020-11-31' and a.customerId = b.customerId and a.status = 6 group by b.logDate

Any help would be appreciated.

Answer 1

Your group by clause is off: you would need to group by all non-aggregated columns.

select s.customerid, s.username, s.serviceid, s.status, max(l.logdate) as maxlogdate
from status s
inner join logs l
where 
    l.logdate >= '2020-10-01' 
    and l.logdate < '2020-12-01' 
    and l.customerid = s.customerid 
    and s.status = 6 
group by s.customerid, s.username, s.serviceid, s.status

Some databases support just putting the primary key of the status table in the group by clause. Assuming that it is customerid:

group by s.customerid 

Note that I changed the query to use meaningful table aliases.