if OBJECT_ID('[sorttable]') is not null drop table [A1]
create table [sorttable]([ID] int,[sort] nvarchar(255))
insert into [sorttable]
select 1,'排序1'
union all
select 2,'排序2'
union all
select 3,'排序3'

if OBJECT_ID('[details]')is not null  drop table [B1]
create table [details]([ID] int,[name] nvarchar(255),[sortid] int,[count] int)
insert into [details]
select 1,'明细1',1,1
union all
select 2,'明细2',1,2
union all
select 3,'明细3',1,3
union all
select 4,'明细4',1,10
union all
select 5,'明细5',2,10
union all
select 6,'明细6',2,20
union all
select 7,'明细7',2,30
union all
select 8,'明细8',2,100
union all
select 9,'明细9',3,101
union all
select 10,'明细10',3,201
union all
select 11,'明细11',3,301
union all
select 12,'明细12',3,1000

select 
    MAX([count]) as [count],
    max([name]) as [name],
    MAX([details].ID) as detailid,
    MAX([sorttable].sort) as sortname 
from 
    [details],[sorttable] where [details].sortid=[sorttable].ID
group by [details].sortid

drop table [sorttable]
drop table [details]

------解决方案--------------------

SQL code

if OBJECT_ID('[sorttable]') is not null drop table [A1]
create table [sorttable]([ID] int,[sort] nvarchar(255))
insert into [sorttable]
select 1,'排序1'
union all
select 2,'排序2'
union all
select 3,'排序3'

if OBJECT_ID('[details]')is not null  drop table [B1]
create table [details]([ID] int,[name] nvarchar(255),[sortid] int,[count] int)
insert into [details]
select 1,'明细1',1,1
union all
select 2,'明细2',1,2
union all
select 3,'明细3',1,3
union all
select 4,'明细4',1,10
union all
select 5,'明细5',2,10
union all
select 6,'明细6',2,20
union all
select 7,'明细7',2,30
union all
select 8,'明细8',2,100
union all
select 9,'明细9',3,101
union all
select 10,'明细10',3,201
union all
select 11,'明细11',3,301
union all
select 12,'明细12',3,1000

SELECT 
 MAX(S.ID) AS ID
,MAX(COUNT) AS 最大数量 
,MAX(name) AS 名字  
,MAX(s.sort) AS 排序名
FROM  details d left join sorttable s on d.sortid = s.ID  GROUP BY SORT
 
/*
 ID          最大数量        名字                                                                                                                                                                                                                                                              排序名
----------- ----------- --------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------
1           10          明细4                                                                                                                                                                                                                                                             排序1
2           100         明细8                                                                                                                                                                                                                                                             排序2
3           1000        明细9