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 onFor example:- The current year is 2021 so the job number will be like this:-
21-1, 21-2, 21-3, 21-4, and so onWhen 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 onI 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 onbut 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 onAnd 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 onI hope I have explained my problem briefly. Please help me in sorting out the job numbers.
Advertisement
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_columnfrom my_tableorder by left(my_column, 2), len(my_column), right(my_column, len(my_column) - 2)Explanation:
left(my_column, 2),numerically sort first two digits so years are grouped together.len(my_column)group record sequence based on magnitude (i.e.,xx-100appears afterxx-2because it’s longer).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)