I'm sure this has been asked before, but since the question can be formulated in many different ways it's difficult to find the proper answer.
I have an orders table with a varchar field for the order number, which is formatted with 4-digits year, a dash (-), and a progressive numeric value. For example it may contain the following values:
SELECT number FROM orders ORDER BY number LIMIT 10;
number ---------- 1999-13 2019-11 2020-1 2020-10 2020-100 2020-12 2020-2 2020-21 2020-3 2021-1
I need to sort that field by year and then by the progressive number, with this expected result:
number ---------- 1999-13 2019-11 2020-1 2020-2 2020-3 2020-10 2020-12 2020-21 2020-100 2021-1
My questions are:
- Which is the simplest way to achieve this with an ORDER subclause?
- How to add an efficient index using this custom sort without having to modify the table?
I would like to keep at least the first answer as database-agnostic as possible (that's why I haven't included db specific tags), but if different best answers are possible for different DBMS/versions, let's assume PostgreSQL 12.