order by character length=4 first, then the others 3

[fifiriri]

Hi all,
I would like to order a query to have all 4 characters returns first (in descending order) then all the others
1 2 10 12 1000 1001 1002 ... 3123 3124 3125 890454 909844 945455
3125 3124 3123 ... 1002 1001 1000 1 2 10 12 890454 909844 945455
or
3125 3124 3123 ... 1002 1001 1000 945455 909844 890454 12 10 2 1

So far I have: Select * from MyTable1 ORDER BY MyCol1 DESC
which gives me: 945455 909844 890454 3125 3124 3123 ... 1002 1001 1000 12 10 2 1

or Select * from MyTable1 WHERE LEN(MyCol1)=4 ORDER BY MyCol1 DESC
but then i have only: 3125 3124 3123 ... 1002 1001 1000

 

[gmmastros]

Select * from MyTable1 ORDER BY CASE WHEN LEN(MyCol1)=4 Then 0 Else 1 End, MyCol1 DESC

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fifiriri]

Thank you