select top 1 * from tablename where order by convert(real,right(col,9)) desc
select max(convert(real,right(col,9)) from tablename
--建立表create table #temp(aa varchar(50)) go ---插入数据 insert into #temp values('AS050512458') insert into #temp values('AQ050512456') insert into #temp values('DC050512434') insert into #temp values('DC050512423') insert into #temp values('AX050512133') insert into #temp values('AQ050510000') select * from #temp go ---查询语句 select top 1 * from #temp order by convert(numeric,right(aa,9)) desc----结果aa -------------------------------------------------- AS050512458(所影响的行数为 1 行)
if exists (select 1 from sysobjects where xtype='U' and name='test') drop table test gocreate table test ( AA varchar(20) ) insert into test values('AS050512458') insert into test values('AQ050512456') insert into test values('DC050512434') insert into test values('DC050512423') insert into test values('AX050512133') insert into test values('AQ050510000')select Top 1 * from test order by right(AA,9) descdrop table test
go
---插入数据
insert into #temp values('AS050512458')
insert into #temp values('AQ050512456')
insert into #temp values('DC050512434')
insert into #temp values('DC050512423')
insert into #temp values('AX050512133')
insert into #temp values('AQ050510000')
select * from #temp
go
---查询语句
select top 1 * from #temp order by convert(numeric,right(aa,9)) desc----结果aa
--------------------------------------------------
AS050512458(所影响的行数为 1 行)
drop table test
gocreate table test
(
AA varchar(20)
)
insert into test values('AS050512458')
insert into test values('AQ050512456')
insert into test values('DC050512434')
insert into test values('DC050512423')
insert into test values('AX050512133')
insert into test values('AQ050510000')select Top 1 * from test order by right(AA,9) descdrop table test