Sort sequence and ORDER BY

 

Sort sequences affect ordering done by the ORDER BY clause. The following SQL statement causes the result table to be sorted using the values in the JOB column:

SELECT * FROM STAFF ORDER BY JOB

The following table shows the result using a *HEX sort sequence. The rows are sorted based on the EBCDIC value in the JOB column. In this case, all lowercase letters sort before the uppercase letters.

Table 1. Result of using the *HEX sort sequence

ID	NAME	DEPT	JOB	YEARS	SALARY	COMM
100	Plotz	42	mgr	6	18352.80	0
90	Koonitz	42	sales	6	18001.75	1386.70
80	James	20	Clerk	0	13504.60	128.20
10	Sanders	20	Mgr	7	18357.50	0
50	Hanes	15	Mgr	10	20659.80	0
30	Merenghi	38	MGR	5	17506.75	0
20	Pernal	20	Sales	8	18171.25	612.45
40	OBrien	38	Sales	6	18006.00	846.55
70	Rothman	15	Sales	7	16502.83	1152.00
60	Quigley	38	SALES	0	16808.30	650.25

The following table shows how sorting is done for a unique-weight sort sequence. After the sort sequence is applied to the values in the JOB column, the rows are sorted. Notice that after the sort, lowercase letters are before the same uppercase letters, and the values 'mgr', 'Mgr', and 'MGR' are adjacent to each other.

Table 2. Result of using the unique-weight sort sequence for the ENU language identifier

ID	NAME	DEPT	JOB	YEARS	SALARY	COMM
80	James	20	Clerk	0	13504.60	128.20
100	Plotz	42	mgr	6	18352.80	0
10	Sanders	20	Mgr	7	18357.50	0
50	Hanes	15	Mgr	10	20659.80	0
30	Merenghi	38	MGR	5	17506.75	0
90	Koonitz	42	sales	6	18001.75	1386.70
20	Pernal	20	Sales	8	18171.25	612.45
40	OBrien	38	Sales	6	18006.00	846.55
70	Rothman	15	Sales	7	16502.83	1152.00
60	Quigley	38	SALES	0	16808.30	650.25

The following table shows how sorting is done for a shared-weight sort sequence. After the sort sequence is applied to the values in the JOB column, the rows are sorted. For the sort comparison, each lowercase letter is treated the same as the corresponding uppercase letter. In this table, notice that all the values 'MGR', 'mgr' and 'Mgr' are mixed together.

Table 3. Result of using the shared-weight sort sequence for the ENU language identifier

ID	NAME	DEPT	JOB	YEARS	SALARY	COMM
80	James	20	Clerk	0	13504.60	128.20
10	Sanders	20	Mgr	7	18357.50	0
30	Merenghi	38	MGR	5	17506.75	0
50	Hanes	15	Mgr	10	20659.80	0
100	Plotz	42	mgr	6	18352.80	0
20	Pernal	20	Sales	8	18171.25	612.45
40	OBrien	38	Sales	6	18006.00	846.55
60	Quigley	38	SALES	0	16808.30	650.25
70	Rothman	15	Sales	7	16502.83	1152.00
90	Koonitz	42	sales	6	18001.75	1386.70

 

Parent topic:

Sort sequence used with ORDER BY and row selection