select top 1 * from tb_notary where left(snotaryno,4)='可靠的靠'
order by cast(substring(snotaryno,charindex('第',snotaryno)+1,charindex('号',snotaryno)-charindex('第',snotaryno)-1) as int) Desc
Union All
select top 1 * from tb_notary where left(snotaryno,4)='可靠事实'
order by cast(substring(snotaryno,charindex('第',snotaryno)+1,charindex('号',snotaryno)-charindex('第',snotaryno)-1) as int) Desc
order by cast(substring(snotaryno,charindex('第',snotaryno)+1,charindex('号',snotaryno)-charindex('第',snotaryno)-1) as int) Desc
Union All
select top 1 * from tb_notary where left(snotaryno,4)='可靠事实'
order by cast(substring(snotaryno,charindex('第',snotaryno)+1,charindex('号',snotaryno)-charindex('第',snotaryno)-1) as int) Desc
insert #t select '可靠的靠第12号'
insert #t select '可靠的靠第3号'
insert #t select '可靠事实可靠第3号'
insert #t select '可靠事实可靠第23号'select left(c, charindex('第',c)-1),
max(cast(substring(c, charindex('第',c)+1, charindex('号',c)-charindex('第',c)-1) as int)) from #t
group by left(c, charindex('第',c)-1)
group by left(你的列,charindex('第',你的列)-1)
select max(cast(substring(你的列,charindex('第',你的列)+1,charindex('号',你的列)-charindex('第',你的列)-1) as int)) from tb_notary
group by left(你的列,charindex('第',你的列)-1)