Now, I don't want to be rude but:

Which is why...

@Phil
You're right Phil. In an attempt to remove unnecessary details from a much more complex query, I forgot to mention some important aspects of the problem.
I'll try...

Thanks Drew, actually your solution works well for the given dataset, but it assumes facts about data:
If tomorrow my user decides to rename the states State_Z and State_A instead...

Hi smugson,
I'm sorry but it doesn't do the trick:

CREATE TABLE #TAB1(
ID INT
,MY_STATE VARCHAR(10)
,EVENT_TS DATETIME
)

INSERT INTO #TAB1(ID, MY_STATE,...

I think Phil's solution works better in the case with more than one states but I'd really like to get the most of this example - for me it's so...

@thom-a uhm, if you insert also the following line:
INSERT INTO #TAB1(ID, MY_STATE, EVENT_TS) VALUES (1, 'STATE_2', DATEADD(DAY,2, GETDATE()))               

the solution you suggested returns STATE_2...

I agree with you @Phil Parkin but in this case the ETL has a hierarchical structure (4 levels). Each team has its own branch and when we work on leaf...

I'd prefer free but I'm willing to invest few hundred bucks if needed.
In case of paid courses I'd like to know if they go staight to the point...

Uhm, got it.. So we'll keep raising hands to get a priority on a development branch. Somehow I was hoping for a more computer-aided solution but your replies are more...

Not sure, because I don't own a copy of 64-bit version.
However, after some research I believe you're right. The best solution would be to upgrade to 64-bit version (extending...

@Chirs yes, the IDs are unique but thanks for the hint regarding the join-free correlation, that way the query is much more readable.

After investigating the issue I finaly...

Hi Lynn,
first of all, my sincere apologies for not posting the question in a better fashion, I'll try to improve next time I post a question.
Thank you for...

Thank you all for the answers:
@scottpletcher: no worries, no '*' in the view 🙂
@Joe_Torre: Before posting I run sp_updatestats but nothing has changed. Am I missing...