Welcome to the Tweaking4All community forums!
When participating, please keep the Forum Rules in mind!
Topics for particular software or systems: Start your topic link with the name of the application or system.
For example “MacOS X – Your question“, or “MS Word – Your Tip or Trick“.
Please note that switching to another language when reading a post will not bring you to the same post, in Dutch, as there is no translation for that post!
When trying to sort text as number in SQLite, you'll run into some issues where it does not sort numerical.
With a little trick we can order text numerically, even for fields that start with a number.
In my example, I had a field "video_mode" with values like "480p", "720p", "1080p" and "2160p".
The normale
ORDER BY video_mode
results in:
1080p
2160p
480p
720p
Not exactly numerical ... or at least not the way we expected it ....
If we add a CAST to INTEGER however, we DO et numerical sorting:
ORDER BY CAST(video_mode AS INTEGER)
The result is the desired order:
480p
720p
1080p
2160p
