select last_name,DEPARTMENT_ID,LAST_NAME,SALARY
from EMPLOYEES
order by DEPARTMENT_ID desc ,SALARY desc
--排序預設為遞增,加上desc變成遞減,希望哪一欄遞減就把desc放在那一欄名稱的後面,所以:
order by DEPARTMENT_ID desc ,SALARY desc :先依DEPARTMENT_ID 遞減、再依salary遞減作排序
last_name | DEPARTMENT_ID | LAST_NAME | SALARY |
Higgins | 110 | Higgins | 12000 |
Gietz | 110 | Gietz | 8300 |
King | 90 | King | 24000 |
Kochhar | 90 | Kochhar | 17000 |
De Haan | 90 | De Haan | 17000 |
order by DEPARTMENT_ID ,SALARY desc :先依DEPARTMENT_ID 遞增、再依salary遞減作排序
last_name | DEPARTMENT_ID | LAST_NAME | SALARY |
Grant | NULL | Grant | 7000 |
Whalen | 10 | Whalen | 4400 |
Hartstein | 20 | Hartstein | 13000 |
Fay | 20 | Fay | 6000 |
select Top(5) last_name,DEPARTMENT_ID,LAST_NAME,SALARY--括號可加可不加
from EMPLOYEES
order by DEPARTMENT_ID ,SALARY
雖然TOP(第幾位)是寫在第一行,但其實是跟著第三行的排序在走的。也就是類似先不要看TOP(5)後把表單列出來,然後再取表單最上面的五筆資料。
以本例來說,取得的是已經先依照ID、薪資遞增過的資料中的最上面五筆,而非依照名字(last_name)排序的五筆資料,也因為他主要是看第三行,所以他就在第一行的最前方,不可更動位置。
last_name | DEPARTMENT_ID | LAST_NAME | SALARY |
Grant | NULL | Grant | 7000 |
Whalen | 10 | Whalen | 4400 |
Fay | 20 | Fay | 6000 |
Hartstein | 20 | Hartstein | 13000 |
Vargas | 50 | Vargas | 2500 |
select Top 20 percent last_name,DEPARTMENT_ID,LAST_NAME,SALARY--亦可以用百分比來查詢
from EMPLOYEES
order by DEPARTMENT_ID ,SALARY desc
last_name | DEPARTMENT_ID | LAST_NAME | SALARY |
Grant | NULL | Grant | 7000 |
Whalen | 10 | Whalen | 4400 |
Hartstein | 20 | Hartstein | 13000 |
Fay | 20 | Fay | 6000 |
select Top 2 with ties last_name,DEPARTMENT_ID,LAST_NAME,SALARY --代表最後面同名次有超過一筆資料時,通通顯示(例如本例只取兩個資料,但因為2跟3的薪資一樣,所以都顯示出來)
from EMPLOYEES
order by SALARY desc
last_name | DEPARTMENT_ID | LAST_NAME | SALARY |
King | 90 | King | 24000 |
Kochhar | 90 | Kochhar | 17000 |
De Haan | 90 | De Haan | 17000 |