I have a table with account numbers and dates (amoung other columns). For each account number there are multiple entries with different dates. I want to return only the row with the most recent date and only account numbers that I have from another source.
This is what I've tried but I understand why this doesn't work. I just can't think of an alternative approach.
SELECT ACCOUNT_NUMBER, MAX(DATE) FROM PORTFOLIO WHERE ACCOUNT_NUMBER IN (444347, 899999, 887111, 220922) GROUP BY ACCOUNT_NUMBER I want to return only the row with the most recent date and only account numbers that I have from another source.
I understand that you want the latest record per account number. If so, one solution is to use analytic functions:
select * from ( select p.*, rank() over(partition by account_number order by date desc) rn from portfolio p where account_number in (444347, 899999, 887111, 220922) ) t where rn = 1 Another approach uses a correlated subquery for filtering:
select p.* from portfolio p where p.account_number in (444347, 899999, 887111, 220922) and p.date = ( select max(p1.date) from portfolio p1 where p1.account_number = p.account_number ) 
SELECT ACCOUNT_NUMBER, DATE FROM PORTFOLIO WHERE ACCOUNT_NUMBER IN (444347, 899999, 887111, 220922) and date = (select max(date) from portfolio b where portfolio.account_number = account_number)