Sort by ID when ID < 1000, otherwise sort by name I have a table of Ids and Names. Where the Id is less than 1000, I want to order by Id. Otherwise sort by Names. For example, if I have:
Id   Name
2    A
1    B
3    C
1111 Z
2222 Y
3333 X

The result should be (first three rows sorted by Id, last three rows sorted by Name):
Id   Name
1    B
2    A
3    C
3333 A
1111 B
2222 C

I came up with this:
SELECT * 
FROM table1
ORDER BY 
  CASE Id
    WHEN Id &lt; 1000 THEN
        Id
    ELSE
        Name
  END
  ASC
;

Which isn't really working - for the following table:
Id   Name
1    M
2    P
3    A1
4    C
5    A2
6    T
1009 A2
1011 A1
1010 A3

I get the following output:
1    M
3    A1
1011 A1
5    A2
1009 A2
1010 A3
4    C
2    P
6    T

What's happening here? And what's the correct query I'm after?
@ypercubeᵀᴹ has the correct answer in a comment.
To have this always be correct -- including cases when the names start with digits -- you need to have a two column order by clause as follows:
SELECT *
FROM table1
ORDER BY
  CASE WHEN Id &lt; 1000 THEN Id ELSE 1000 END,
  Name;

Observe that all Id's above 1000 are assigned the same value so that then the second column comes into play.