if object_id('dbo.[Message]') is not null
	drop table dbo.[Message]
go

-- table
create table dbo.[Message](
	Id int identity(1,1) primary key
	, FromUserId int not null -- в реальной базе - FK на таблицю User
	, ToUserId int not null -- в реальной базе - FK на таблицю User
	, [Date] datetime default getdate() not null
	, Body nvarchar(100) not null
	, IsRead bit default 0
)
go

-- data
insert into dbo.[Message](FromUserId, ToUserId, [Date], Body, IsRead)
values
	(1, 2, '2015-01-23 22:00:01', N'privet', 1)
	, (2, 1, '2015-01-23 22:00:02', N'zdorov', 1)
	, (1, 2, '2015-01-23 22:00:03', N'kak ona?', 0)
	, (2, 1, '2015-01-23 22:00:04', N'good', 0)
	, (3, 1, '2015-01-23 21:00:00', N'zanyat?', 0)
	, (4, 5, '2015-01-23 22:00:06', N's fantaziey problemy', 1)
	, (2, 1, '2015-01-23 22:00:07', N's fantaziey problemy', 1)
go

select m.FromUserId, count(1) as UnreadMessageQty from dbo.[Message] m where m.ToUserId = 1 and m.IsRead = 0 group by m.FromUserId

select top(10) * from dbo.Message m where m.FromUserId = 1 or m.ToUserId = 1 order by m.[Date] desc

select * from (
	select * from dbo.Message m where (m.FromUserId = 2 and m.ToUserId = 1 and m.IsRead = 0) 
	union
	select * from (
		select top(10) * from dbo.Message m where (m.FromUserId = 1 and m.ToUserId = 2) or (m.FromUserId = 2 and m.ToUserId = 1) order by m.[Date] desc
	) as top10
) msg
order by msg.[Date] desc

select top(10) uc.UserId, min(uc.IsRead) as IsRead
from (	
	select top(20) -- берем в 2 раза больше, чтоб исключить дубликаты
		case when ac.FromUserId = 1 then ac.ToUserId else ac.FromUserId end as UserId
		, case when	ac.ToUserId = 1 then ac.minRead else 1 end as IsRead -- если сообщение не прочитано
		, ac.maxDate
	from (
		select 
			m.FromUserId, m.ToUserId, min(m.IsRead) as minRead, max(m.Date) as maxDate
		from dbo.Message m where m.FromUserId = 1 or m.ToUserId = 1
		group by m.FromUserId, m.ToUserId
	) ac -- all contacts
	order by ac.minRead, ac.maxDate desc
) uc -- unique contacts
group by uc.UserId
order by min(uc.IsRead), max(uc.maxDate)

select FromUserId, LastMoment, count(*) 
from (
	select 
		m.FromUserId,
		MAX(m.Date) over(partition by m.FromUserId) LastMoment
	from dbo.Message m
	where 1=1
	  and ToUserId = @currentUserId
	  and IsRead = 0) t
group by FromUserId, LastMoment
order by FromUserId, LastMoment