IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
Hi,
I will explain ORDER BY in Oracle SQL in this post of Oracle SQL Tutorial series.
Read the previous post of this tutorial series before this.
AND, OR and NOT Operators in Oracle SQL | Oracle SQL Tutorials -5
The ORDER BY is most popular keyword in Oracle SQL that is used to sort the result as ascending and descending order.
ORDER BY Syntax
SELECT column1, column2, ...FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
Order by keyword sorts the results in ascending order by default, so If you sort your Results in ascending order, no need to use ASC ( Ascending )
You can use Order by keyword as follows.
SQL> SELECT DEPARTMENT_ID, FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE DEPARTMENT_ID<50 ORDER BY SALARY;
DEPARTMENT_ID FIRST_NAME LAST_NAME SALARY
------------- -------------------- ------------------------- ----------
30 Karen Colmenares 2500
30 Guy Himuro 2600
30 Sigal Tobias 2800
30 Shelli Baida 2900
30 Alexander Khoo 3100
10 Jennifer Whalen 4400
20 Pat Fay 6000
40 Susan Mavris 6500
30 Den Raphaely 11000
20 Michael Hartstein 13000
10 rows selected.
SQL>
Using ASC or not is not important, both of them are the same result as follows.
SQL> SELECT DEPARTMENT_ID, FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE DEPARTMENT_ID<50 ORDER BY SALARY ASC;
DEPARTMENT_ID FIRST_NAME LAST_NAME SALARY
------------- -------------------- ------------------------- ----------
30 Karen Colmenares 2500
30 Guy Himuro 2600
30 Sigal Tobias 2800
30 Shelli Baida 2900
30 Alexander Khoo 3100
10 Jennifer Whalen 4400
20 Pat Fay 6000
40 Susan Mavris 6500
30 Den Raphaely 11000
20 Michael Hartstein 13000
10 rows selected.
SQL>
You can sort your result in Descending ( DESC ) as follows.
SQL> SELECT DEPARTMENT_ID, FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE DEPARTMENT_ID<50 ORDER BY SALARY DESC;
DEPARTMENT_ID FIRST_NAME LAST_NAME SALARY
------------- -------------------- ------------------------- ----------
20 Michael Hartstein 13000
30 Den Raphaely 11000
40 Susan Mavris 6500
20 Pat Fay 6000
10 Jennifer Whalen 4400
30 Alexander Khoo 3100
30 Shelli Baida 2900
30 Sigal Tobias 2800
30 Guy Himuro 2600
30 Karen Colmenares 2500
10 rows selected.
SQL>
ORDER BY Several Columns
You can sort your result set order by several columns as follows.
SQL>
SQL> SELECT DEPARTMENT_ID, FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE DEPARTMENT_ID<50 ORDER BY SALARY DESC, DEPARTMENT_ID ASC;
DEPARTMENT_ID FIRST_NAME LAST_NAME SALARY
------------- -------------------- ------------------------- ----------
20 Michael Hartstein 13000
30 Den Raphaely 11000
40 Susan Mavris 6500
20 Pat Fay 6000
10 Jennifer Whalen 4400
30 Alexander Khoo 3100
30 Shelli Baida 2900
30 Sigal Tobias 2800
30 Guy Himuro 2600
30 Karen Colmenares 2500
10 rows selected.
SQL>
The second example is as follows.
SQL> SELECT DEPARTMENT_ID, FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE DEPARTMENT_ID<50 ORDER BY DEPARTMENT_ID DESC, SALARY DESC;
DEPARTMENT_ID FIRST_NAME LAST_NAME SALARY
------------- -------------------- ------------------------- ----------
40 Susan Mavris 6500
30 Den Raphaely 11000
30 Alexander Khoo 3100
30 Shelli Baida 2900
30 Sigal Tobias 2800
30 Guy Himuro 2600
30 Karen Colmenares 2500
20 Michael Hartstein 13000
20 Pat Fay 6000
10 Jennifer Whalen 4400
10 rows selected.
SQL>
The third example is as follows.
SQL> SELECT DEPARTMENT_ID, FIRST_NAME, LAST_NAME, SALARY FROM HR.EMPLOYEES WHERE DEPARTMENT_ID<50 ORDER BY SALARY ASC, FIRST_NAME DESC;
DEPARTMENT_ID FIRST_NAME LAST_NAME SALARY
------------- -------------------- ------------------------- ----------
30 Karen Colmenares 2500
30 Guy Himuro 2600
30 Sigal Tobias 2800
30 Shelli Baida 2900
30 Alexander Khoo 3100
10 Jennifer Whalen 4400
20 Pat Fay 6000
40 Susan Mavris 6500
30 Den Raphaely 11000
20 Michael Hartstein 13000
10 rows selected.
SQL>
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
