declare @t table (message_id int, from_id int, to_id int, send_date datetime, txt nvarchar(max))
insert into @t values ( 1,1,2,'2000-1-1 10:00:00', '...')
insert into @t values ( 2,2,1,'2000-1-1 10:00:05', '...')
insert into @t values ( 3,1,2,'2000-1-1 10:00:08', '...')
insert into @t values ( 4,2,1,'2000-1-1 10:00:11', '...')
insert into @t values ( 5,1,3,'2000-1-1 10:00:02', '...')
insert into @t values ( 6,3,1,'2000-1-1 10:00:04', '...')
insert into @t values ( 7,1,3,'2000-1-1 10:00:07', '...')
insert into @t values ( 8,1,2,'2000-1-1 10:00:13', '...')
insert into @t values ( 9,2,1,'2000-1-1 10:00:14', '...')
insert into @t values (10,1,2,'2000-1-1 10:00:20', '...')
insert into @t values (11,2,3,'2000-1-1 10:00:00', '...')
insert into @t values (12,1,4,'2000-1-1 10:00:10', '...')
insert into @t values (12,5,1,'2000-1-1 10:00:20', '...')
select t0.*
from
(
select isnull(to_id, from_id) as userid,
case when to_id is not null and from_id is not null and t0.d > t1.d then t0.d
when to_id is not null and from_id is not null and t0.d < t1.d then t1.d
when to_id is not null and from_id is null then t0.d
when to_id is null and from_id is not null then t1.d
else null end as date,
case when to_id is not null and from_id is not null and t0.d > t1.d then 1
when to_id is not null and from_id is not null and t0.d < t1.d then 2
when to_id is not null and from_id is null then 1
when to_id is null and from_id is not null then 2
else null end as S
from
(
select to_id, max(send_date) as d, 1 as s
from @t
where from_id = 1
group by to_id
) as t0
full outer join
(
select from_id, max(send_date) as d, 2 as s
from @t
where to_id = 1
group by from_id
) as t1
on t0.to_id = t1.from_id
) as T
inner join @t t0 on T.date=t0.send_date and
T.userid=case S when 1 then t0.to_id
when 2 then t0.from_id end
robilem testy na ms sql server, dlatego uzycie zmiennej tabelarycznej, zasta ja zwykla tabela i bedzie ok
moza ewentualnie pomyslec nad uzyciem rank