pick the first result for each parameter

I have a table with account_number, account_code and code_date I am trying to get the latest code for each account, but the group by is giving me multiple observation for each account, thus I can’t even use the first/top statement.

example:

account_id  Account_Attribute_Code   current_att_date
1                579                      01.01.2005
1                254                      01.02.2006
1                366                      10.10.2018
2                748                      01.07.2008
2                766                      08.05.2009
2                205                      07.06.2014

 SELECT 
       account_id, 
       Account_Attribute_Code,
       Max(Account_Attribute_Update_Date) AS current_att_date
 FROM my_table
 GROUP BY Account_Id, 
          Account_Attribute_Code

I want to have only 1 row for each account, with his attribute_code where the date is the most current.

Answer

You can just use a correlated subquery:

select t.*
from t
where t.Account_Attribute_Update_Date = (select max(t2. Account_Attribute_Update_Date)
                                         from t t2
                                         where t2.account_id = t.account_id
                                        );

There are multiple approaches to this problem. With an index on (account_id, Account_Attribute_Update_Date), then this normally has the best performance.

One caveat: If there are duplicate maximum dates for an account, you will get multiple rows.