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!