Select max and group by only one column

Question

I'm struggling to select multiple columns while using a max function because I only want it to group by one column.

Here is my dataset:

UPDATED_DATE    ACCOUNT_NUMBER     LIMIT
------------    --------------     -----
2020-02-01      ABC123             100
2020-02-06      ABC123             300
2020-03-04      XYZ987             500
2020-05-19      XYZ987             100

Here are the results I'm hoping to see:

UPDATED_DATE    ACCOUNT_NUMBER     LIMIT
------------    --------------     -----
2020-02-06      ABC123             300
2020-05-19      XYZ987             100

I appreciate the help.

Answer 1

You can use a window functions:

select t.*
from (select t.*, row_number() over partition by account_number order by updated_date desc) as seqnum
      from t
     ) t
where seqnum = 1;

Or -- a method that typically has slightly better performance with the right indexes --:

select t.*
from t
where t.updated_date = (select max(t2.updated_date) from t t2 where t2.account_number = t.account_num);

Or, if you don't like subqueries and don't care so much about performance:

select top (1) with ties t.*
from t
order by row_number() over (partition by account_number order by updated_date desc);