0

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

A DB fiddle is here.

My questions are:

  1. Which is the simplest way to achieve this with an ORDER subclause?
  2. 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.

3
  • which rdms are you using?
    – nbk
    Nov 20 '20 at 12:30
  • @nbk it's written in the question (last line!) :) Nov 20 '20 at 12:33
  • 1
    You can use a collation, see here. Nov 20 '20 at 15:25
3

I would convert that value to an integer array, then sort on that array:

SELECT number 
FROM orders 
ORDER BY string_to_array(number, '-')::int[] 
LIMIT 10;

Online example

2
  • Looks nice! I would choose this answer unless there is a more agnostic statement that works at least in the latest major RDBMS (namely Oracle, PostgreSQL, MySQL, SQLite and Microsoft). Nov 20 '20 at 13:14
  • What about adding an index? Nov 20 '20 at 13:22
0

You can splitthe number and sort by it parts

CREATE TABLE test  
    ("number" varchar(8))
;
    
INSERT INTO test  
    ("number")
VALUES
    ('1999-13'),
    ('2019-11'),
    ('2020-1'),
    ('2020-10'),
    ('2020-100'),
    ('2020-12'),
    ('2020-2'),
    ('2020-21'),
    ('2020-3'),
    ('2021-1')
;
SELECT "number"
FROM test
ORDER BY 
split_part("number", '-', 1)::numeric,split_part("number", '-', 2)::numeric;
| number   |
| :------- |
| 1999-13  |
| 2019-11  |
| 2020-1   |
| 2020-2   |
| 2020-3   |
| 2020-10  |
| 2020-12  |
| 2020-21  |
| 2020-100 |
| 2021-1   |

db<>fiddle here

0

I have an orders table with a varchar field for the order number ... year ... dash ... progressive numeric value

Sorry, but I would suggest that this is a classic case of storing multiple pieces of information in a single database field - and that's a Bad Idea.

Most order tables I've ever seen simply use a single, numeric field to uniquely identify the order and include the order date as a separate field, from which the year can be derived, if required.

2
  • Unfortunately we live in a real, not ideal, world. Order numbers are not user defined and I need to store them as is. It's a real world data and needs its own field. Furthermore there are legal requirements on them (should be unique and progressive for every year) and the company uses its own convention (one adopted by many other companies tbh) and won't change it "because it's a bad idea". I suggested to continue viewing them by issue date but there are some problems I don't need to explain here. Nov 20 '20 at 14:58
  • Pretty much expected that to be the case. In that case I would consider the use of Postgres's Generated Columns to "intercept" the insertion of these underperforming, composite values and to replicate them into individual, sensible fields that can be more efficiently processed.
    – Phill W.
    Nov 20 '20 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.