Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
sorting 5
- Thread starter Mandy_crw
- Start date
Chriss Miller
Programmer
- Aug 14, 2024
- 66
Right align the values, i.e. store " 1", not "1 ", " 2", not "2 ", etc.
Or simpler: Use an integer or numeric field for grades.
Or simpler: Use an integer or numeric field for grades.
-
1
- #3
Joe Crescenzi
Programmer
If the values are text based instead of numbers, 1, 10, 11, 100, or even 10000000 will sort before 2.
You will either need to make them numbers, or pad the text based values with zeros or even spaces before sorting.
The number of pads will depend on your largest value. So, if your largest value is 100, all your numbers need to be padded 3 characters.
You will either need to make them numbers, or pad the text based values with zeros or even spaces before sorting.
The number of pads will depend on your largest value. So, if your largest value is 100, all your numbers need to be padded 3 characters.
Code:
So this...
? PADL(1,3)
? PADL(12,3)
? PADL(123,3)
Will look like this...
1
12
123-
1
- #4
I've had this situation several times years ago. What I got in the habit of doing was using a leading zero in my text fields so I would have values that looked like this:
01
02
03
04
05
06
07
08
09
10
11
12
13...
That's if I wished to keep the values as text. Otherwise, just use the val() function in your sort. Either way will give you the expected results.
Keep On Codin'
Pastor Steve
01
02
03
04
05
06
07
08
09
10
11
12
13...
That's if I wished to keep the values as text. Otherwise, just use the val() function in your sort. Either way will give you the expected results.
Keep On Codin'
Pastor Steve
EinTerraner
Technical User
in case this field has no additional chars except the numeric ones just set INDEX ON VAL(fieldname)
Chriss Miller
Programmer
- Aug 14, 2024
- 66
Mandy,
I hope the major point is clear now. You're defining the grade field as character, so text sorting rules.
You do expect a dictionary to sort all words with a before all words with b, don't you? You don't expect the dictionary to sort a,b,c,d,e,f,..,z,aa,ab,ac,.., etc.
Do you? Would you like to first need to count the number of letters of a word to be able to look it up in a dictionary? No, you don't. Or do you?
In text sorting the inital letter (also if that's a digit) has the major importance, in number notation that's also true, in general, but the longer the numbers get the higher the weight of the first digit, i.e. a three-digit number starting with 1 comes before a two-digit number, even if that begins with 9. That's not so with words, the word length of a word doesn't scale the weight of letters, all a-words come before all b-words, no matter if they are longer or shorter.
So you can only expect numeric sortfing for numeric fields or formatting text so the text sorting has the same order as numeric sorting or you determine the text value, that will sort text which represents numbers correctly, but would treat every other text as 0, not sorting it or sorting all text between positive and negative numbers.
It's a very general rule of working without surprises like that to store data in the appropriate data type only, ever. You store numbers in integer, numeric, float, currency field type - what you need is specific, not only when you want to calculate with it but also when you want sort numerically vs alphabetically, compare them. And grades are numbers that you calculate with, normally, when you calculate the average grade, for example. That's also best done when storing grades in one of the available number formats. As average grades have decimal places, it would even not be wrong to store the simple integer grades into float fields, just so that you could also store an averyge of a class or of all homework of a student into such a field, too.
I hope the major point is clear now. You're defining the grade field as character, so text sorting rules.
You do expect a dictionary to sort all words with a before all words with b, don't you? You don't expect the dictionary to sort a,b,c,d,e,f,..,z,aa,ab,ac,.., etc.
Do you? Would you like to first need to count the number of letters of a word to be able to look it up in a dictionary? No, you don't. Or do you?
In text sorting the inital letter (also if that's a digit) has the major importance, in number notation that's also true, in general, but the longer the numbers get the higher the weight of the first digit, i.e. a three-digit number starting with 1 comes before a two-digit number, even if that begins with 9. That's not so with words, the word length of a word doesn't scale the weight of letters, all a-words come before all b-words, no matter if they are longer or shorter.
So you can only expect numeric sortfing for numeric fields or formatting text so the text sorting has the same order as numeric sorting or you determine the text value, that will sort text which represents numbers correctly, but would treat every other text as 0, not sorting it or sorting all text between positive and negative numbers.
It's a very general rule of working without surprises like that to store data in the appropriate data type only, ever. You store numbers in integer, numeric, float, currency field type - what you need is specific, not only when you want to calculate with it but also when you want sort numerically vs alphabetically, compare them. And grades are numbers that you calculate with, normally, when you calculate the average grade, for example. That's also best done when storing grades in one of the available number formats. As average grades have decimal places, it would even not be wrong to store the simple integer grades into float fields, just so that you could also store an averyge of a class or of all homework of a student into such a field, too.
Last edited:
-
1
- #7
Create the following index on your field that contains the grade value (where grade is the field name):
Now use this index to set the order for display. If you want to select the records then use the same PADL() expression in the ORDER BY clause.
Code:
INDEX ON PADL(ALLTRIM(grade), 10, '0') TAG gradeNow use this index to set the order for display. If you want to select the records then use the same PADL() expression in the ORDER BY clause.
-
1
- #8
igorsandler
Programmer
If you need to sort a view or a cursor, you can add an additional field to it INT(VAL(grade)) AS igrade_sort. If you need to order a table then you need to create an index, like it was suggested above. I am using it all the time
-
1
- #9
index on str(val(grade),2) tag tag1
- Thread starter
- #10
Similar threads
- Locked
- Question
