Sort by ID when ID < 1000, otherwise sort by name
@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.