数据分组结果问题，紧急求救啊

[junoon]

待处理结果：
002006009
002006
002008002
002001
002
001003001
取前六位，期望结果
001
001003
002
002001
002008
002006

[cd731107]

select left(字段名,6) from tb

[cd731107]

取前六位，期望结果
001 是怎么来的？

[GoAwayZ]

select left(col,6) from tb order by left(col,6)

[cd731107]

select top 6 * from tb
order by 字段名

[SQL77]

（所影响的行数为 6 行）

a         
--------- 
001
001003
002
002001
002006
002008

（所影响的行数为 6 行）

[SQL77]

--> 测试数据：#t
if object_id('tempdb.dbo.#t') is not null drop table #t
create table #t([a] varchar(9))
insert #t
select '002006009' union all
select '002006' union all
select '002008002' union all
select '002001' union all
select '002' union all
select '001003001'

select a from 
(
select distinct left(a,3) as a,0 as rn from #t
union 
select distinct left(a,6) as a,1 as rn from #t
)t
group by a
order by max(rn)
/*
（所影响的行数为 6 行）

a         
--------- 
001
001003
002
002001
002006
002008

（所影响的行数为 6 行）

[hanger1212]

substring(string,1,6) 取1到6位

[junoon]

3位一级。补充下，001003001的父级是001003，001003的父级是001
故要取出001和001003

[cd731107]

[Quote=引用 8 楼 junoon 的回复:]
3位一级。补充下，001003001的父级是001003，001003的父级是001
故要取出001和001003
[/Quote]

select left(字段名,6) from tb
union 
select left(字段名,3) from tb

[--小F--]

select case when len(col)=3 then LEFT(col,3) else  LEFT(col,6)  end as a  from tb

[rucypli]

select len(a,3) as b from tb
union
select len(a,6) as bfrom tb
order by b