Order By子句

今天要講解的是得到查詢的資料後，將資料做排序，或者進階使用排序進行資料的串接與比對
select A.*
from Table A
where 1=1
order by A.Col1 ASC(DESC), A.Col2 ASC(DESC)
**[註1]**可直接用欄位順序1,2,3取代欄位名稱做排序
**[註2]**在欄位後方透過 ASC(升冪, 預設) or DESC(降冪) 指定排序方式
**[註3]**在MySQL中，NULL被視為最小值，但在oracle中，可使用 NULLS LAST或者NULLS FIRST 決定 NULL 屬於最小or最大值

• example

with class as (
    select 'Amy' name, 'CSIE' dept from dual
    union all
    select 'Oleve' name, 'Languages' dept from dual
    union all
    select 'Jake' name, 'CSIE' dept from dual
    union all
    select 'Freddy' name, 'History' dept from dual
    union all
    select null name, null dept from dual
    union all
    select 'Jerry' name, null dept from dual
    union all
    select null name, 'Chemical' dept from dual
)
-- 1.原始無排序狀態
select A.*
from class A
;
-- output
NAME   DEPT
------ ---------
Amy    CSIE
Oleve  Languages
Jake   CSIE
Freddy History
(null) (null)
Jerry  (null)
(null) Chemical

-- 2.排序 & name null排最後面
select A.*
from class A
where 1=1
order by 1 desc NULLS LAST, 2 asc
;
-- output
NAME   DEPT
------ ---------
Oleve  Languages
Jerry  (null)
Jake   CSIE
Freddy History
Amy    CSIE
(null) Chemical
(null) (null)

-- 3.排序 & name null排最前面
select A.*
from class A
where 1=1
order by 1 desc NULLS FIRST, 2 asc
;
-- output
NAME   DEPT
------ ---------
(null) Chemical
(null) (null)
Oleve  Languages
Jerry  (null)
Jake   CSIE
Freddy History
Amy    CSIE

限制資料輸出量-ROWNUM

在查詢資料的時候，將資料排序過後，還可以搭配rownum這個保留字限縮輸出資料筆數。

• 題目: 取出學校內數學排名最高分的前三名

with math_grade as (
    select 'Amy' name, 'CSIE' dept, 50 score from dual
    union all
    select 'Oleve' name, 'Languages' dept, 59 score from dual
    union all
    select 'Jake' name, 'CSIE' dept, 60 score from dual
    union all
    select 'Freddy' name, 'History' dept, 80 score from dual
    union all
    select 'Kamy' name, null dept, 80 score from dual
    union all
    select 'Jerry' name, null dept, 60 score  from dual
    union all
    select 'Sherry' name, 'Chemical' dept, 60 score from dual
    union all
    select 'Ban' name, 'Chemical' dept, 85 score from dual
    union all
    select 'Seafood' name, 'Medicine' dept, 90 score from dual
    union all
    select 'Celine' name, 'Medicine' dept, 95 score  from dual
)
select A.*
from(
  select A.*
  from math_grade A
  order by 3 desc, 1 desc NULLS FIRST, 2 asc
)A
where 1=1
and rownum<=3
;
-- output
NAME	DEPT	       SCORE
------- --------- ----------
Celine	Medicine	  95
Seafood Medicine	  90
Ban	    Chemical	  85

注意：rownum只能使用在where子句，因此若需要先排序後再選擇前X名，要將rownum寫在最外層，否則會變成以下結果:

select A.*
from math_grade A
where 1=1
and rownum<=3
order by 3 desc, 1 desc NULLS FIRST, 2 asc
-- output
NAME	DEPT	       SCORE
------- --------- ----------
Jake	CSIE		  60
Oleve	Languages	  59
Amy	    CSIE		  50