求将Varchar字段按某种其他规则排序的查询语句
现有一表，内容为文件相关信息字段为：
kind——文件所属目录
filename——文件全名
filesize——文件长度
查询时需要按目录、文件全名进行排序，但文件全名排序时需要将所有.reg的文件排在最后结果如：

folder1   a.txt   2253
folder1   x.bat   105
folder1   c.reg   226
folder2   b.xml   965
folder2   m.exe   6357
folder2   d.reg   335
folder2   e.reg   100
所有以reg结尾的文件按照文件名升序都排在大类最后，其他文件名按升序排在前面。

请各位高手为小弟解惑，谢谢！

------解决方案--------------------
create table #t(kind varchar(20),filename varchar(20),filesize int)
insert into #t
select 'folder1 ', 'a.txt ',2253
union all select 'folder1 ', 'x.bat ',105
union all select 'folder1 ', 'c.reg ',226
union all select 'folder2 ', 'b.xml ',965
union all select 'folder2 ', 'm.exe ',6357
union all select 'folder2 ', 'd.reg ',335
union all select 'folder2 ', 'e.reg ',100

select * from #t
order by kind,case when charindex( '.reg ',filename)> 0 then 'zzzzzzzz.zzz ' else filename end
/*
kind filename filesize
-------------------- -------------------- -----------
folder1 a.txt 2253
folder1 x.bat 105
folder1 c.reg 226
folder2 b.xml 965
folder2 m.exe 6357
folder2 d.reg 335
folder2 e.reg 100

（所影响的行数为 7 行）
*/
------解决方案--------------------

create table #(kind varchar(20),filename varchar(20),filesize int)
insert into # select 'folder1 ' , 'a.txt ', 2253 union all
select 'folder1 ', 'x.bat ', 105 union all
select 'folder1 ' , 'c.reg ', 226 union all
select 'folder2 ', 'b.xml ', 965 union all
select 'folder2 ', 'm.exe ', 6357 union all
select 'folder2 ' , 'd.reg ', 335 union all
select 'folder2 ', 'e.reg ', 100

select * from # order by kind,charindex( '.reg ',filename),filename


--------
folder1 a.txt 2253
folder1 x.bat 105
folder1 c.reg 226
folder2 b.xml 965
folder2 m.exe 6357
folder2 d.reg 335
folder2 e.reg 100
------解决方案--------------------
select * from tt
order by kind,iif(charindex( '.reg ',filename)> 0 then 'zzzzzzzz.zzz ',filename),
substring(filename,1,charindex( '. ',filename)-1)