Introduction
The sorting options are used (amongst others) by following functions:
- table sort columns()
- table sort rows()
- table sort selected rows()
- table rank rows()
- table rank selected rows()
Overview of Available Options
| Sorting / Ranking Option | Description |
|---|---|
| alphabetic | Alphabetic order by UNICODE character set. |
| alphabetic down | Opposite direction of above |
| alphabetic ignore case | Alphabetic order, but ignores case (e.g. 'the Hague' comes before 'Uruguay') |
| alphabetic down ignore case | Opposite direction of above |
| alphabetic ignore blanks | Same as alphabetic. Blanks (incl. new line, tabs, space) are ignored. 'St. Moritz' and 'St.Moritz' are equivalent. |
| alphabetic down ignore blanks | Opposite direction of above |
| alphabetic ignore both | Same as alphabetic, ignoring both case and blanks |
| alphabetic down ignore both | Opposite direction of above |
| numeric | Sorts by numbers. Non-numeric contents are interpreted like zero. Smallest number comes first. |
| numeric down | Opposite direction of above |
| date | Sorts by dates. Fields containing no qualified dates are interpreted like blank dates. See rules below the table. |
| date down | Opposite direction of above |
| time | Sorts by dates. Fields containing no qualified dates are interpreted like blank dates. See rules below the table. |
| time down | Opposite direction of above |
| date and time | Sorts by dates. Fields containing no qualified dates are interpreted like blank dates. See rules below the table. |
| date and time down | Opposite direction of above |
Rules on strings:
- Blank string '' comes first
- One space symbol ' ' (UNICODE 32) comes next.
- Sorting is done based on UNICODE character codes.
- Sorting takes no cultural implications into consideration (e.g. Spanish: LL follows L).
- Numbers are also treated as strings: 0100 comes before 10.
Rules on numerals:
- For contents beginning a number: The 1st number will be recognized.
- For contents beginning with non-numeric symbols: Interpreted as zero.
Rules on dates:
- Blank dates come first.
- If year is missing, current year is assumed.
- If day is missing, the 1st day of month is assumed.
- Month names in key languages like "Jan", "feb", "März", "avril", "May", "juni", etc. are recognized as months and sorted accordingly.
- Local settings are used as rules to resolve country dependent date formats like 01/02/2015 (day and month ordering: 2. Jan 2015 or 1. Feb 2015). Local settings are in the system variables and can be changed.
- Time values in the date are ignored. E.g. "14.07.2016 10:00" and "14.07.2016 11:00" are treated as identical values
Rules on dates and times:
- Blank dates come first.
- Date values without time come before date values with time (e.g. "00:00:00") if date are on the same day.
- Time values are not ignored
Rules on times:
- Blank dates come first.
- Date values are ignored. "14.07.2016 10:00� and “15.07.2016 10:00� are treated as identical values
Following example shows a ranking based on the different options applied.
table initialize ( t,
{ { Scheme, alphabetic, alphabetic down, alphabetic ignore case, alphabetic down ignore case, numeric, numeric down, date, date down, time, time down },
"02-Dec-2020 10:00", "02-Nov-2020 11:00", "05", "1", "Dec", "apr 15", "-1", "", "begin" } );
for all parameters( [t:1..,0], option[] ) table rank rows( t, option[], Scheme, option[], 113 ); // 113 - Same ranking share same ranking position
table transpose (t);
table list (t); 0 : Scheme | 02-Dec-2020 10:00 | 02-Nov-2020 11:00 | 05 | 1 | Dec | apr 15 | -1 | | begin
1 : alphabetic | 3 | 4 | 5 | 6 | 7 | 8 | 2 | 1 | 9
2 : alphabetic down | 7 | 6 | 5 | 4 | 3 | 2 | 8 | 9 | 1
3 : alphabetic ignore case | 3 | 4 | 5 | 6 | 9 | 7 | 2 | 1 | 8
4 : alphabetic down ignore case | 7 | 6 | 5 | 4 | 1 | 3 | 8 | 9 | 2
5 : numeric | 7 | 7 | 9 | 6 | 2 | 2 | 1 | 2 | 2
6 : numeric down | 2 | 2 | 1 | 4 | 5 | 5 | 9 | 5 | 5
7 : date | 8 | 7 | 1 | 1 | 9 | 6 | 1 | 1 | 1
8 : date down | 2 | 3 | 5 | 5 | 1 | 4 | 5 | 5 | 5
9 : time | 8 | 9 | 5 | 5 | 1 | 1 | 5 | 1 | 1
10 : time down | 2 | 1 | 3 | 3 | 6 | 6 | 3 | 6 | 6