How to sort numbers containing current year in prefix

I am having trouble sorting the job numbers in my project. My client requirement is to store the job numbers in the below format:-

current year-1, current year-2, current year-3, and so on...

For example:- The current year is 2021 so the job number will be like this:-

21-1, 21-2, 21-3, 21-4, and so on...

When the year changes it should start again from 1 and so on for the next year. For example:-

22-1, 22-2, 22-3, 22-4, and so on...

I had stored the job numbers in the above format successfully but I am unable to sort the job numbers in the correct way as required by the client. I had sorted the data in this way:-

21-1, 21-10, 21-100 to 21-109, 21-11 to 21-19, 21-2 and so on...

but the actual sort should be like this:-

21-1, 21-2, 21-3, 21-4...21-10, 21-11 to 21-99, 21-100 to 21-199 and so on...

And if the year changes then:-

22-1, 22-2, 22-3, 22-4...22-10, 22-11 to 22-99, 22-100 to 22-199 and so on...

I hope I have explained my problem briefly. Please help me in sorting out the job numbers.

Answer

I assume you need the sorting to occur in your database because you’re paging or just otherwise not holding all results in application memory, use use the following sql order by clause:

select    my_column
from      my_table
order by  left(my_column, 2),
          len(my_column),
          right(my_column, len(my_column) - 2)

Explanation:

1. left(my_column, 2), numerically sort first two digits so years are grouped together.
2. len(my_column) group record sequence based on magnitude (i.e., xx-100 appears after xx-2 because it’s longer).
3. right(my_column, len(my_column) - 2) numerically sort record sequence.

Hint: This assumes your year-code is always exactly two digits. I could have found the index of the dash instead, but that feels even more presumptive.

---

If you require an application-side (PHP) solution, you can use natsort. From W3Schools:

Definition and Usage

The natsort() function sorts an array by using a “natural order” algorithm. The values keep their original keys.

In a natural algorithm, the number 2 is less than the number 10. In computer sorting, 10 is less than 2, because the first number in “10” is less than 2.

Syntax

natsort(array)