Cat | Rank | Value
 A      1     11
 A      2     12
 A      3     14

Cat | Rank | Avg
 A  |  2   | 12.3

set nocount on 

create table #moo (cat varchar(1), rank int, value real)

insert into #moo values ('a',3,14)
insert into #moo values ('a',2,12)
insert into #moo values ('a',1,11)
insert into #moo values ('b',1,44)


select d.cat, d.rank, f.avgr from 
(
select a.cat, a.rank, value - avgr as diff  from #moo a
inner join 
(select cat, avg(value) as avgr from #moo group by cat) b
on a.cat = b.cat
) d
Inner join 
(
select cat,  min(diff * diff) as unsigneddiff from 
(
select a.cat, a.rank, value - avgr as diff  from #moo a
inner join 
(select cat, avg(value) as avgr from #moo group by cat) b
on a.cat = b.cat
) b
group by cat) e
on  d.diff * d.diff = unsigneddiff
and d.cat = e.cat

inner join 
(select cat, avg(value) as avgr from #moo group by cat) 
f
on  d.cat = f.cat 

drop table #moo

declare @TempTable table
(cat varchar(1), rank int, value numeric(9,2))

insert into @TempTable values ('A',1,11)
insert into @TempTable values ('A',2,12)
insert into @TempTable values ('A',3,14)
insert into @TempTable values ('B',1,63)
insert into @TempTable values ('B',2,55)
insert into @TempTable values ('B',3,65)

select cat, 
(select top 1 rank from @TempTable b where a.cat = b.cat order by abs(a.avgval - b.value) asc) AS NearestMatch,
avgval from
(
	select cat, avg(value) as avgval
	from @TempTable 
	group by cat
) a