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.
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!
I believe you can use RANK() window function for that, then you probably can remove GROUP BY / DISTINCT clause.
SELECT sid, date FROM (
SELECT t2.sid
, t1.date
, RANK() OVER(ORDER BY t1.date DESC) AS row_num
FROM dbo.table1 t1
LEFT JOIN dbo.table2 t2 ON t1.id = t2.id
WHERE t1.num = 000
AND t2.sid NOT LIKE '%#########%'
) as date_ranked_by_id
WHERE date_ranked_by_id.row_num != 1