ORDER BY in Oracle SQL | Oracle SQL Tutorials -6

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 )

 592 views last month,  1 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Check Also

Create Table ( CTAS ) and Data Types, LOB Data Types in Oracle SQL | Oracle SQL Tutorials -27

Hi, I will explain Create Table ( CTAS ) and Data Types in Oracle SQL in …

Leave a Reply