Sort sequence and row selection

 

Sort sequences affect selection of data. The following SQL statement selects rows with the value 'MGR' in the JOB column:

SELECT * FROM STAFF WHERE JOB='MGR'

The first table shows how row selection is done with a *HEX sort sequence. The rows that match the row selection criteria for the column JOB are selected exactly as specified in the select statement. Only the uppercase 'MGR' is selected.

Table 1. Result of using the *HEX sort sequence

ID	NAME	DEPT	JOB	YEARS	SALARY	COMM
30	Merenghi	38	MGR	5	17506.75	0

Table 2 shows how row selection is done with a unique-weight sort sequence. The lowercase and uppercase letters are treated as unique. The lowercase 'mgr' is not treated the same as uppercase 'MGR'. Therefore, the lowercase 'mgr' is not selected.

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

ID	NAME	DEPT	JOB	YEARS	SALARY	COMM
30	Merenghi	38	MGR	5	17506.75	0

The following table shows how row selection is done with a shared-weight sort sequence. The rows that match the row selection criteria for the column 'JOB' are selected by treating uppercase letters the same as lowercase letters. Notice that all the values 'mgr', 'Mgr' and 'MGR' are selected.

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

ID	NAME	DEPT	JOB	YEARS	SALARY	COMM
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

 

Parent topic:

Sort sequence used with ORDER BY and row selection