SQL select the first match in an ordered list

Question

Using Microsoft SQL Server Management Studio version 14.0.17213.0

I have a list of events that go in order. I want to select the highest precedent acct_no, complete_date and event.

My problem is if I use

select 
    account_number, event, max(complete_date) as mx_comp
from 
    mytable
where 
    event in ('event1','event2'....)

then I get all my acct_numbers, all the events in the list and the max complete date for that event. But I want acct_no listed with the maximum completed date for any item in the list and the associated event.

Furthermore, its wholly possible that two events occurred on the same date, so I cannot do

select * 
from mytable mt
join
    (select acct_number, max(complete_date) 
     from mytable) t on mt.acct_number = t.account_number 
                     and mt.complete_date = t.complete_date

because if two events occurred on the same day then I still get duplicate results.

I have tried to do a similar thing with

    row_number() over (order by account_number) as RowNum

but it did not work, because I still get matches to all the events, not just my highest precedence event

it really boils down to needing to return the acct_number, event and complete date associated to the highest importance match from items in an ordered list.

I am sure it is easy - I just cannot seem to figure it out and despite all my google and stack searching I simply cannot figure it out

I have recently been thinking that it might be possible with something like coalesce(mylist) because I would be able to put my list in order but I cannot figure out how to use coalesce in a meaningful way for this problem.

The real solution would be to create a table with precedence numbers or have a most recent indicator but I dont have unlimited access to create any tables I want.

Any help or ideas on how to match to an ordered list would be appreciated

Answer 1

You seem to want:

select t.*
from (select t.*,
            row_number() over (partition by account_number order by complete_date desc) as seqnum
      from mytable t
      where event in ('event1', 'event2', ....)
     ) t
where seqnum = 1;