InOracle DatabaseWhen we execute a query, we usually need to sort the query results. SortORDERClause. This article summarizes the sorting of Oracle Database queries. Next we will introduce these.
Sort by pinyin (default)
Select * from perexl order by nlssort (danwei, 'nls _ SORT = SCHINESE_PINYIN_M ');
Sort by department heads
Select * from perexl order by nlssort (danwei, 'nls _ SORT = SCHINESE_STROKE_M ');
Sort by strokes
Select * from perexl order by nlssort (danwei, 'nls _ SORT = SCHINESE_RADICAL_M ');
Obtain the first row of data after sorting
Select * from (select * from perexl order by nlssort (danwei, 'nls _ SORT = SCHINESE_PINYIN_M ') C where rownum = 1
Sort in descending order
Select * from perexl order by zongrshu desc
Sort in ascending order
Select * from perexl order by zongrshu asc
Always put nulls at the beginning
Select * from perexl order by danwei nulls first
Always put nulls at the end
Select * from perexl order by danwei desc nulls last
The decode function is more powerful than the nvl function. It can also convert an input parameter to a specific value if it is null.
Select * from perexl order by decode (danwei, null, 'unit is null', danwei)
Usage of standard rownum paging Query
Select * from (select c. *, rownum rn from personnel c) where rn> = 1and rn <= 5
Solution for sorting paging in oracle statement rownum
Select * from (select c. *, rownum rn from personnel c order by date of birth) where rn> = 1and rn <= 5
However, if order by is added, the data is incorrectly displayed. The solution is to add a new query.
Select * from (select rownum rn, t. * from (select name, date of birth from personnel order by date of birth desc) t) where rn> = 1and rn <= 5
If efficiency is to be taken into account, the above can also be optimized to the main difference between the two)
Select * from (select rownum rn, t. * from (select name, date of birth from personnel order by date of birth desc) t where rownum <= 10) where rn> = 3
The nvl function can convert an input parameter to a specific value when it is null. The following is how to convert the unit to "null" when the unit is null"
Select * from perexl order by nvl (danwei, 'unit: null ')
Here is a summary of Oracle Database sorting. If you want to know