Sort by ID when ID < 1000, otherwise sort by name

sqlite

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 < 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?

Best Answer

@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 < 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.

Related Question