求一sql语句 多谢帮忙 急！！！
id code name age 
--------------------
1 001 a 11
2 002 b 13
3 001001 c 14
4 001002 d 15
怎么才能通过 id 1 取得code前缀为001的最大的6位code呢？

------解决方案--------------------
select top 6 * from table
where code like '001%' and id = '1'
order by code desc
------解决方案--------------------
select top 6 * from table 
where code like '001%' and id = '1' order by code desc
------解决方案--------------------
select top 6 * from table 
where left(code,3)='001' and id = '1' order by code desc
------解决方案--------------------

探讨

select top 6 * from table
where code like '001%' and id = '1'
order by code desc

------解决方案--------------------
select * from tb where len(code)=6 and left(code,3)='001'
------解决方案--------------------

探讨

select * from tb where len(code)=6 and left(code,3)='001'

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

SQL code

select * from tb where len(code)=6 and left(code,3)='001' and id=1

------解决方案--------------------
SQL code
select max(code) from tb where len(code)=6 and left(code,3)='001'

------解决方案--------------------
SQL code

select top 1 code from ss where code like (select code+'___' from ss where id=1) order by code desc

------解决方案--------------------
SQL code

select * from tb where len(code)=6 and left(code,3)=（select code from tb where [id]=1）order by code desc