Skip to content

Instantly share code, notes, and snippets.

@sentenza

sentenza/version_natural_sorting_mysql.sql

Forked from alfred-dub/version_natural_sorting_mysql.sql
Created August 15, 2017 14:16
Show Gist options
  • Save sentenza/94dbd50929db851c564ef377efb0c268 to your computer and use it in GitHub Desktop.
Save sentenza/94dbd50929db851c564ef377efb0c268 to your computer and use it in GitHub Desktop.
Download ZIP
Natural ordering of migrations' versions using MySQL and Postgre
Raw
version_natural_sorting_mysql.sql
SELECT
SUBSTRING_INDEX(number, '.', 1) AS FIRST_INT,
SUBSTRING_INDEX(SUBSTRING_INDEX(number, '.', 2), '.', -1) AS SECOND_INT,
SUBSTRING_INDEX(number, '.', -1) AS THIRD_INT,
number as VERSION,
description as TITLE
FROM _version
ORDER BY
LENGTH(SUBSTRING_INDEX(number, '.', 1)) DESC,
SUBSTRING_INDEX(number, '.', 1) DESC,
LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(number, '.', 2), '.', -1)) DESC,
SUBSTRING_INDEX(SUBSTRING_INDEX(number, '.', 2), '.', -1) DESC,
LENGTH(SUBSTRING_INDEX(number, '.', -1)) DESC,
SUBSTRING_INDEX(number, '.', -1) DESC
Raw
version_natural_sorting_postgresql.sql
SELECT
number as VERSION,
split_part(number, '.', 1) AS FIRST_INT,
split_part(number, '.', 2) AS SECOND_INT,
split_part(number, '.', 3) AS THIRD_INT,
description as TITLE
FROM _version
ORDER BY
split_part(number, '.', 1)::int DESC
split_part(number, '.', 2)::int DESC
split_part(number, '.', 3)::int DESC
@sentenza
Copy link
Author

sentenza commented Sep 6, 2017 •
edited
Loading

Natural vs Alphabetical sort order

Normally, the default sorting order used is the numerical/alphabetical one. Postgresql can do much more because it's possible to cast string into integers and vice versa.

About the natural sorting take a look at this coding horror post

For PgSQL see also this SO answer: https://stackoverflow.com/a/9173594/1977778

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment