When working with databases, displaying query results in an ordered fashion is extremely important for reporting and data analysis. The SQL ORDER BY clause allows you to freely sort data in ascending or descending order, and even by multiple conditions.
This article provides a thorough explanation, from the basics of the ORDER BY clause to practical examples. We have aimed for clear explanations with diagrams and concrete examples so that even beginners can use it immediately.
This article uses examples based on the SCOTT sample schema. You can import and use the SCOTT sample schema by executing the following:
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlsampl.sql
$ sqlplus scott/tiger
1. What is the ORDER BY Clause? Why is it Important?
The ORDER BY clause is a clause used to sort the data retrieved by an SQL query. Sorting serves the following purposes:
- Improved Data Visibility: Quickly find important information from large amounts of data.
- Report Generation: Enables ranking and trend analysis.
- Data Validation: Confirm the range and distribution of data.
2. Basic Syntax and Usage
First, let’s review the basic syntax.
SELECT column_name1, column_name2, ...
FROM table_name
ORDER BY column_name [ASC|DESC];
- ASC: Ascending order (default setting). Ex: 1, 2, 3, …
- DESC: Descending order. Ex: 3, 2, 1, …
3. Concrete Examples Using the ORDER BY Clause
Here, we will use the EMP table included in the Oracle Database SCOTT schema.
Sample Data: EMP table
SQL> SELECT ename,job,sal,deptno
2 FROM emp;
ENAME JOB SAL DEPTNO
---------- --------- ---------- ----------
SMITH CLERK 800 20
ALLEN SALESMAN 1600 30
WARD SALESMAN 1250 30
JONES MANAGER 2975 20
MARTIN SALESMAN 1250 30
BLAKE MANAGER 2850 30
CLARK MANAGER 2450 10
SCOTT ANALYST 3000 20
KING PRESIDENT 5000 10
TURNER SALESMAN 1500 30
ADAMS CLERK 1100 20
JAMES CLERK 950 30
FORD ANALYST 3000 20
MILLER CLERK 1300 10
14 rows selected.
3.1 Basic Example: Sorting in Ascending Order
To sort employee salaries (SAL) in ascending order, write the following:
SELECT ENAME, SAL
FROM EMP
ORDER BY SAL;
Result:
SQL> SELECT ENAME, SAL
2 FROM EMP
3 ORDER BY SAL;
ENAME SAL
---------- ----------
SMITH 800
JAMES 950
ADAMS 1100
WARD 1250
MARTIN 1250
MILLER 1300
TURNER 1500
ALLEN 1600
CLARK 2450
BLAKE 2850
JONES 2975
SCOTT 3000
FORD 3000
KING 5000
14 rows selected.
3.2 Applied Example: Sorting in Descending Order
Next, let’s sort SAL in descending order.
SELECT ENAME, SAL
FROM EMP
ORDER BY SAL DESC;
Result:
SQL> SELECT ENAME, SAL
2 FROM EMP
3 ORDER BY SAL DESC;
ENAME SAL
---------- ----------
KING 5000
FORD 3000
SCOTT 3000
JONES 2975
BLAKE 2850
CLARK 2450
ALLEN 1600
TURNER 1500
MILLER 1300
WARD 1250
MARTIN 1250
ADAMS 1100
JAMES 950
SMITH 800
14 rows selected.
3.3 Sorting by Multiple Columns
If you want to sort data by multiple criteria, specify multiple columns in the ORDER BY clause. For example, to sort by department number (DEPTNO) in ascending order, and then by salary (SAL) in descending order within the same department:
SELECT ENAME, DEPTNO, SAL
FROM EMP
ORDER BY DEPTNO ASC, SAL DESC;
Result:
SQL> SELECT ENAME, DEPTNO, SAL
2 FROM EMP
3 ORDER BY DEPTNO ASC, SAL DESC;
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
CLARK 10 2450
MILLER 10 1300
SCOTT 20 3000
FORD 20 3000
JONES 20 2975
ADAMS 20 1100
SMITH 20 800
BLAKE 30 2850
ALLEN 30 1600
TURNER 30 1500
MARTIN 30 1250
WARD 30 1250
JAMES 30 950
14 rows selected.
3.4 Sorting Using Column Numbers (Not Recommended)
Using column numbers is concise, but it is less readable than using column names. The following is an example of sorting by the 3rd column (SAL) in ascending order and the 1st column (ENAME) in descending order.
SELECT ENAME, SAL, DEPTNO
FROM EMP
ORDER BY 3, 1 DESC;
Result:
SQL> SELECT ENAME, SAL, DEPTNO
2 FROM EMP
3 ORDER BY 3, 1 DESC;
ENAME SAL DEPTNO
---------- ---------- ----------
MILLER 1300 10
KING 5000 10
CLARK 2450 10
SMITH 800 20
SCOTT 3000 20
JONES 2975 20
FORD 3000 20
ADAMS 1100 20
WARD 1250 30
TURNER 1500 30
MARTIN 1250 30
JAMES 950 30
BLAKE 2850 30
ALLEN 1600 30
14 rows selected.
(Note: The query ORDER BY 3, 1 DESC sorts by the 3rd column DEPTNO ascending, then the 1st column ENAME descending.)
3.5 Handling NULL Values
When using the ORDER BY clause, it is also possible to specify the position of NULL values.
- NULLS FIRST: Sorts NULL values to the beginning.
- NULLS LAST: Sorts NULL values to the end (default setting).
SELECT ENAME, SAL
FROM EMP
ORDER BY SAL NULLS FIRST;
Result:
SQL> SELECT ENAME, SAL
2 FROM EMP
3 ORDER BY SAL NULLS FIRST;
ENAME SAL
---------- ----------
SMITH 800
JAMES 950
ADAMS 1100
WARD 1250
MARTIN 1250
MILLER 1300
TURNER 1500
ALLEN 1600
CLARK 2450
BLAKE 2850
JONES 2975
SCOTT 3000
FORD 3000
KING 5000
14 rows selected.
(Note: The SAL column in the EMP table contains no NULLs, so the effect of NULLS FIRST is not visible in this specific output, but the syntax is correct.)
4. Practical Application Points
Report Generation When you want to create a sales ranking and extract the top 10:
SELECT PRODUCT_NAME, SALES
FROM SALES_TABLE
ORDER BY SALES DESC
FETCH FIRST 10 ROWS ONLY;
Trend Analysis Analyze the latest trends by sorting sales over time.
SELECT SALE_DATE, TOTAL_SALES
FROM DAILY_SALES
ORDER BY SALE_DATE DESC;
Performance Optimization When sorting large amounts of data, utilize indexes. Create appropriate indexes on the columns used for sorting.
5. Points to Note
- Performance with Large Data: Sorting requires resources. If sorting a large dataset, narrow down the target with a WHERE clause or utilize indexes.
- Prioritize Readability: Use column names instead of column numbers to clarify the query’s intent.
6. Summary and Next Steps
In this article, we explained everything from the basics of the ORDER BY clause to its practical applications. With this knowledge, you can freely organize data and apply it to analysis and report generation.
[reference]
Oracle Database SQL Language Reference, 19c
コメント