Summary of the use of the order by clause in Oracle Database sorting

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