Connoisseur
Platinum Member
Hey guys. Have a sorting logic question and I apologize for the amount of text but I want to describe the issue properly. We have data elements in a table (well we're exporting the data elements from an index file) along with a "position" field. This field is tree-based and indicates the order in which the data elements were stored into the database. So for Example:
ElementA 1-1
ElementB 1-2
In this case A entered the system as be. To provide a more complex example:
ElementA 1-2-5-6-8
ElementB 1-2-10-1-1
ElementC 0-2-4-6-8
ElementD 3-1
In this case, Element C was loaded before A, A was loaded before B and D was loaded last. Basically the logic is, you start with the left most digit before the dash, sort, grab the next digits after the dash, sort and continue sorting until you're out of numbers. The solutions I can think of all have serious problems:
1) The sorting method across millions of records would be way too inefficient and processor intensive.
2) I considered concatenating the numbers to make an integer and sort but there's a lot of logical errors that can be generated due to the combinations of numbers and the variability of the string length. You'll have instances where a later element would be ranked first.
I'm hoping someone smarter than me would have an easy, FAST solution to properly sort this field in SQL. This field is also stored in an index file and can be called using a java method which stores each of the positions in an array. So some java code could theoretically solve this problem as well. The key issues here are of course quickness and accuracy. Any thoughts?
ElementA 1-1
ElementB 1-2
In this case A entered the system as be. To provide a more complex example:
ElementA 1-2-5-6-8
ElementB 1-2-10-1-1
ElementC 0-2-4-6-8
ElementD 3-1
In this case, Element C was loaded before A, A was loaded before B and D was loaded last. Basically the logic is, you start with the left most digit before the dash, sort, grab the next digits after the dash, sort and continue sorting until you're out of numbers. The solutions I can think of all have serious problems:
1) The sorting method across millions of records would be way too inefficient and processor intensive.
2) I considered concatenating the numbers to make an integer and sort but there's a lot of logical errors that can be generated due to the combinations of numbers and the variability of the string length. You'll have instances where a later element would be ranked first.
I'm hoping someone smarter than me would have an easy, FAST solution to properly sort this field in SQL. This field is also stored in an index file and can be called using a java method which stores each of the positions in an array. So some java code could theoretically solve this problem as well. The key issues here are of course quickness and accuracy. Any thoughts?
