I am looking to exclude the most recent date in my SQL query results. I’ve done some research on this subject and can only find examples where they GROUP BY an attribute in the originating table. I GROUP BY an attribute in a joined table.

These are a couple of the solutions I have tried, but I am having a hard time morphing their solution to my query because of my JOIN.

• How do I exclude results with the most recent date?

• SQL query for all records that are not the most recent

This is currently where I am at (pseudo query below). I assumed that I could just change IN to NOT IN and I would thereby exclude the MAX date, but that doesn’t seem to be the right logic:

SELECT t2.sid
,t1.date
FROM dbo.table1 t1 
LEFT JOIN dbo.table2 t2 ON t1.id = t2.id 
WHERE t1.num = 000
AND t2.sid IS NOT NULL
AND t2.sid NOT LIKE '%#########%'
AND t1.date NOT IN (
    SELECT MAX(t1a.date)
    FROM dbo.table1 t1a
    WHERE t1a.id = t1.id
)
GROUP BY t2.sid,t1.date

Any insight is greatly appreciated. Please let me know if I can provide more info. Thanks!