CODEWITHSUNDEEP
sqldatabasesqlitesorting
ZakTaccardi
ZakTaccardi

Reputation: 12467

SQLITE: How do I sort a String column containing values 1-99 and letters a-z, with numbers first then letters

I want a sort order for a string column like the following (numbers first, letters last):

1
2
...
10
11
...
A
B
C
...

Here's my current query

SELECT * FROM PAGES_VIEW ORDER BY chapterTitle

Unfortunately, this results in the following ordering (10 before 2)

1
10
2
20
...
A
B
...

How do I achieve my desired order?

EDIT: removed my CAST as integer to avoid confusion.

Upvotes: 3

Views: 1408

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You should use a case statement to get the desired ordering.

Fiddle with sample data

select * from pages_view 
order by (case when cast(chaptertitle as integer) = chaptertitle then 1
         else 0 end) desc, cast(chaptertitle as integer), chaptertitle

Upvotes: 2

Related Questions