Summary of sorting in Oracle

-- Sort by characters

Select * From perexl order by perexl. xx

-- Sort by numbers corresponding to characters

Select * From perexl order by to_number (perexl. xx)

-- 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

-- However, if order by is added, the data is incorrectly displayed.

Select * from (select C. *, rownum rn from personnel C order by date of birth) Where rn> = 1and rn <= 5

-- Solution: add a layer of query to solve the problem.

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

-- Nvl function can convert the 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 ')