SQL learning uses order by to sort by specified order or custom order, sqlorder

SQL learning uses order by to sort by specified order or custom order, sqlorder

We usually need to provide the customer with a custom sorting method for the query results based on the customer's needs. What are the implementation methods of SQL, the following is a summary of more information (incomplete and incorrect ):

1. if we only need to sort applications in a program in the following way, we only need to set the sorting method at the SQL statement level:

1. sort by default of sorted LEDs: select * from table_name order by col_name (desc | asc );

2. sort by custom order: select * from table_name order by decode (col_name, 'value1 ', 1, value2', 2, value3', 3, value4 ', 4 ,... valuen', N );

2. If we only need to sort applications in a program in the following way, we only need to set the sorting method at the SQL statement level (Generally, the default sorting method for Chinese in our system is sorted by pinyin.):

1. SORT by pinyin: select * fromtable_name order by nlssort (col_name, 'nls _ SORT = SCHINESE_PINYIN_M ');
2. SORT by strokes: select * from table_name order by nlssort (col_name, 'nls _ SORT = SCHINESE_STROKE_M ');
3. SORT by department heads: select * from table_name order by nlssort (col_name, 'nls _ SORT = SCHINESE_RADICAL_M ');
Note: however, when the data volume is large, the query speed will be slow and further optimization is required. According to the oracle official documentation, when oracle builds an index on a Chinese column, it is sorted by BINARY encoding. Therefore, if NLS_SORT is set to BINARY, the index can be used for sorting. if the preceding three types of special sorting for Chinese are used instead of binary sorting, oracle cannot use indexes and performs full table scanning. the solution is to create a linguistic index on this column. for example, create index nls_index ON my_table (NLSSORT (name, 'nls _ SORT = SCHINESE_PINYIN_M '));

Original reference: Note:

Setting NLS_SORT to anything other than BINARY causes a sort to use a full table scan, regardless of the path chosen by the optimizer. BINARY is the exception because indexes are built according to a binary order of keys. thus the optimizer can use an index to satisfy the order by clause when NLS_SORT is set to BINARY. if NLS_SORT is set to any linguistic sort, the optimizer must include a full table scan and a full sort in the execution plan.

3. If we want to sort the fields in a specific way throughout the Session, we need to set the default sorting method for the fields at the Session level:
1. Press pinyin: alter session set nls_sort = SCHINESE_PINYIN_M;
2. Stroke: alter session set nls_sort = SCHINESE_STROKE_M;
3. By the offset: alter session set nls_sort = NLS_SORT = SCHINESE_RADICAL_M;

4. If we need to specify the sorting method for the entire data, we need to modify the system parameters of the oracle server (generally we use less of this method ): 
1. Windows System to modify the Registry HKLC \ SOFTWARE \ ORACLE \ home0 \ NLS_SORT

2. Other configuration changes: set NLS_SORT = SCHINESE_RADICAL_M export NLS_SORT

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.