Select MAX date using data from several columns SQL

Question

I know this is a much asked question and I've had a look through whats already available but I believe my case is slightly unique (and if it's not please point me in the right direction).

I am trying to find the latest occurrence of a row associated to a user a currently across two tables and several columns.

table: statusUpdate
+-------+-----------+-----------+-------------------+
|   id |    name    |   status  |       date_change |
+-------+-----------+-----------+-------------------+
|   1   |   Matt    |       0   |   01-01-2001      |
|   2   |   Jeff    |       1   |   01-01-2001      |
|   3   |   Jeff    |       2   |   01-01-2002      |
|   4   |   Bill    |       2   |   01-01-2001      |
|   5   |   Bill    |       3   |   01-01-2004      |
+-------+-----------+-----------+-------------------+

table: relationship
+-------+-----------+--------------+
|   id  |   userID  |stautsUpdateID|       
+-------+-----------+--------------+
|   1   |   22      |       1      |   
|   2   |   33      |       2      |   
|   3   |   33      |       3      |   
|   4   |   44      |       4      | 
|   5   |   44      |       5      | 
+-------+-----------+--------------+

There is a third table which links userID to its own table but these sample tables should be good enough to get my question over.

I am looking to get the latest status change by date. The problem currently is that it returns all instances of a status change.

Current results:
+-------+---------+-----------+-------------------+
|userID |statusID |   status  |       date_change |
+-------+---------+-----------+-------------------+
|   33  |   2     |       1   |   01-01-2001      |
|   33  |   3     |       2   |   01-01-2002      |
|   44  |   4     |       2   |   01-01-2001      |
|   44  |   5     |       3   |   01-01-2004      |
+-------+---------+-----------+-------------------+

Expected results:
+-------+-----------+-----------+-------------------+
|userID |statusID   |   status  |       date_change |
+-------+-----------+-----------+-------------------+
|   33  |   3       |       2   |   01-01-2002      |
|   44  |   5       |       3   |   01-01-2004      |
+-------+-----------+-----------+-------------------+

I hope this all makes sense, please ask for more information otherwise.

Just to reiterate I just want to return the latest instance of a users status change by date.

Sample code of one of my attempts:

select 
st.ID, st.status, st.date_change, r.userID 
from statusUpdate st
inner join Relationship r on st.ID = r.statusUpdateID

inner join (select ID, max(date_change) as recent from statusUpdate
group by ID) as y on r.stausUpdateID = y.ID and st.date_change = 
y.recent

Hope someone can point me in the right direction.

Answer 1

use row_number() to get the last row by user

select *
from
(
select st.ID, st.status, st.date_change, r.userID,
       rn = row_number() over (partition by r.userID order by st.date_change desc) 
from   statusUpdate st
       inner join Relationship r on st.ID = r.statusUpdateID
) as d
where rn  = 1

Answer 2

I ADDED MAX condition to your answer

CREATE TABLE #Table1
        ([id] int, [name] varchar(4), [status] int, [date_change] datetime)
    ;

INSERT INTO #Table1
    ([id], [name], [status], [date_change])
VALUES
    (1, 'Matt', 0, '2001-01-01 00:00:00'),
    (2, 'Jeff', 1, '2001-01-01 00:00:00'),
    (3, 'Jeff', 2, '2002-01-01 00:00:00'),
    (4, 'Bill', 2, '2001-01-01 00:00:00'),
    (5, 'Bill', 3, '2004-01-01 00:00:00')
;


CREATE TABLE #Table2
    ([id] int, [userID] int, [stautsUpdateID] int)
;

INSERT INTO #Table2
    ([id], [userID], [stautsUpdateID])
VALUES
    (1, 22, 1),
    (2, 33, 2),
    (3, 33, 3),
    (4, 44, 4),
    (5, 44, 5)


select 
max(st.ID) id , max(st.status) status , max(st.date_change) date_change, r.userID 
from #Table1 st
inner join #Table2 r on st.ID = r.stautsUpdateID

inner join (select ID, max(date_change) as recent from #Table1
group by ID) as y on r.stautsUpdateID = y.ID and st.date_change = 
y.recent
group by  r.userID 

output

id  status  date_change               userID
1   0       2001-01-01 00:00:00.000    22
3   2       2002-01-01 00:00:00.000    33
5   3       2004-01-01 00:00:00.000    44