➤ EMP DEPT Tables Queries
➤ Set Operators in Oracle
➤ CREATE command
➤ RENAME command
➤ DROP command
➤ Default Value in Column
➤ ABS() in Oracle SQL
➤ CEIL() in Oracle SQL
➤ SYSDATE in Oracle
➤ Trunc Oracle date
➤ TO_CHAR() in Oracle
➤ GROUP BY Clause
➤ Having Clause
➤ ORDER BY Clause
➤ Constraints in Oracle
➤ Rename Constraint
➤ Disable Constraint
➤ Drop Constraint
➤ NOT NULL Constraint
➤ UNIQUE Constraint
➤ PRIMARY KEY Constraint
➤ FOREIGN KEY Constraint
➤ CHECK Constraint
➤ Unlock User in SQL Plus
➤ Find SID in Oracle database
➤ Check Database Version
➤ Check Database Size
➤ Error ORA 01031
In Oracle SQL the ORDER BY clause is an optional clause which is used to arrange data in either ascending order or in descending order.
Syntax for ORDER BY clause:-
ORDER BY { column-Name | ColumnPosition | Expression }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
[, { column-Name | ColumnPosition | Expression }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
] *Along with the ORDER BY clause, we can also use optional ASC or DESC attributes. The attribute ASC is used with the ORDER BY clause to display data in ascending order and the DESC attribute is used to display data in descending order.
If we don’t specify ASC/DESC attribute with the ORDER BY clause then by default data is displayed in ascending order. But to display in descending order we must use “DESC”
In SELECT statement it can be used as,
SELECT columnName
FROM tablename
ORDER BY columnName [ ASC / DESC ];Examples of Oracle SQL ORDER BY
Let us understand emp table under Scott user of Oracle database.
SQL> SELECT ename, sal, comm, deptno FROM emp;
ENAME SAL COMM DEPTNO
---------- ---------- ---------- ----------
SMITH 800 20
ALLEN 1600 300 30
WARD 1250 500 30
JONES 2975 20
MARTIN 1250 1400 30
BLAKE 2850 30
CLARK 2450 10
SCOTT 3000 20
KING 5000 10
TURNER 1500 0 30
ADAMS 1100 20
JAMES 950 30
FORD 3000 20
MILLER 1300 10
14 rows selected.Example of oracle order by asc (ascending order),
SQL> SELECT sal, deptno
FROM emp
ORDER BY sal;
SAL DEPTNO
---------- ----------
800 20
950 30
1100 20
1250 30
1250 30
1300 10
1500 30
1600 30
2450 10
2850 30
2975 20
3000 20
3000 20
5000 10
14 rows selected.Example of oracle order by desc (descending order),
SQL> SELECT sal, deptno
FROM emp
ORDER BY sal DESC;
SAL DEPTNO
---------- ----------
5000 10
3000 20
3000 20
2975 20
2850 30
2450 10
1600 30
1500 30
1300 10
1250 30
1250 30
1100 20
950 30
800 20
14 rows selected.SQL> SELECT sal, deptno
FROM emp
ORDER BY deptno DESC;
SAL DEPTNO
---------- ----------
2850 30
1500 30
1600 30
1250 30
1250 30
950 30
3000 20
2975 20
800 20
1100 20
3000 20
5000 10
1300 10
2450 10
14 rows selected.Column Position Used in Oracle ORDER BY Clause
In the ORDER BY clause we can also use column position which is used to improves the performance of the query. For Example:- While selecting ename, sal, deptno from emp the sal is the second column so we sal use 2 instead of sal with ORDER BY clause.
SQL> SELECT ename, sal, deptno
FROM emp
ORDER BY 2 DESC;
ENAME SAL DEPTNO
---------- ---------- ----------
KING 5000 10
FORD 3000 20
SCOTT 3000 20
JONES 2975 20
BLAKE 2850 30
CLARK 2450 10
ALLEN 1600 30
TURNER 1500 30
MILLER 1300 10
WARD 1250 30
MARTIN 1250 30
ADAMS 1100 20
JAMES 950 30
SMITH 800 20
14 rows selected.The above query is similar as,
SQL> SELECT ename, sal, deptno
FROM emp
ORDER BY sal DESC;Another example,
SQL> SELECT sal
FROM emp
ORDER BY 1 DESC;While displaying all records of actual emp table in scott user of Oracle database, the deptno is 8th column so in place of deptno we can also use 8.
SQL> SELECT * FROM emp ORDER BY 8 DESC;It is similar to,
SQL> SELECT * FROM emp ORDER BY deptno DESC;Sorting Multiple Columns in Oracle
We can use sort multiple columns using the ORDER BY clause but in this case database server sorting data based on the first column within the order by clause.
SQL> SELECT deptno, sal
FROM emp
ORDER BY deptno DESC, sal DESC;
DEPTNO SAL
---------- ----------
30 2850
30 1600
30 1500
30 1250
30 1250
30 950
20 3000
20 3000
20 2975
20 1100
20 800
10 5000
10 2450
10 1300
14 rows selected.Both deptno, and sal columns are sorted in descending order but first column deptno has more priority, and second column is sorted based on first column.
SQL> SELECT sal, deptno
FROM emp
ORDER BY sal DESC, deptno DESC;
SAL DEPTNO
---------- ----------
5000 10
3000 20
3000 20
2975 20
2850 30
2450 10
1600 30
1500 30
1300 10
1250 30
1250 30
1100 20
950 30
800 20
14 rows selected.Another example using both ASC / DESC attributes,
SQL> SELECT deptno, sal
FROM emp
ORDER BY deptno, sal DESC;
DEPTNO SAL
---------- ----------
10 5000
10 2450
10 1300
20 3000
20 3000
20 2975
20 1100
20 800
30 2850
30 1600
30 1500
30 1250
30 1250
30 950
14 rows selected.Oracle first sorts the rows by deptno in ascending order to make an initial result set. Then it sorts the initial result set by the sal in descending order.
The default order is ascending order so, “ORDER BY deptno, sal DESC” is similar to “ORDER BY deptno ASC, sal DESC;”
Sorting NULL and Non-Null
The attributes NULLS FIRST or NULLS LAST of ORDER BY clause are used to display all null value in the given column FIRST or LAST respectively before the non-null values.
If we don’t specify it then default values are,
- NULLS LAST for ASC
- NULLS FIRST for DESC.
Example using NULLS FIRST and ASC attribute,
SQL> SELECT sal, comm
FROM emp
ORDER BY comm NULLS FIRST;
SAL COMM
---------- ----------
800
2450
3000
950
1100
2975
2850
1300
3000
5000
1500 0
1600 300
1250 500
1250 1400
14 rows selected.While using asc,
SQL> SELECT sal, comm
FROM emp
ORDER BY comm;
SAL COMM
---------- ----------
1500 0
1600 300
1250 500
1250 1400
3000
5000
1100
950
3000
1300
2850
2975
800
2450
14 rows selected.If we don’t specify ASC/DESC attribute then ASC is the default attribute, and similarly if we don’t specify NULLS FIRST or NULLS LAST for the ASC attribute then NULLS last is the default.
SQL> SELECT sal, comm
FROM emp
ORDER BY comm DESC;
SAL COMM
---------- ----------
800
2450
3000
950
1100
2975
2850
1300
3000
5000
1250 1400
1250 500
1600 300
1500 0
14 rows selected.Example of ORDER BY clause using expression,
SQL> SELECT sal, comm, sal+nvl(comm, 0)
FROM emp
ORDER BY sal+nvl(comm, 0);
SAL COMM SAL+NVL(COMM,0)
---------- ---------- ---------------
800 800
950 950
1100 1100
1300 1300
1500 0 1500
1250 500 1750
1600 300 1900
2450 2450
1250 1400 2650
2850 2850
2975 2975
3000 3000
3000 3000
5000 5000
14 rows selected.It can be also written with alias name,
SQL> SELECT sal, comm, sal+nvl(comm, 0) as X
FROM emp
ORDER BY x;Other points
While working with SELECT statement the ORDER BY clause should be used at the end of the SELECT statement. The complete syntax of select query is,
SELECT columnName
FROM tablename
WHERE condition
GROUP BY columnName
HAVING condition
ORDER BY columnName [ASC / DESC];
Example:-
SQL> SELECT deptno, count(*)
FROM emp
WHERE sal > 1000
GROUP BY deptno
HAVING count(*) > 3
ORDER BY deptno DESC;
DEPTNO COUNT(*)
---------- ----------
30 5
20 4Note:- If SELECT DISTINCT is specified or if the SELECT statement contains a GROUP BY clause, the ORDER BY columns must be in the SELECT list. Example:-
SQL> SELECT ename, sal, comm
FROM emp
GROUP BY deptno
ORDER BY deptno;
SELECT ename, sal, comm
*
ERROR at line 1:
ORA-00979: not a GROUP BY expressionThe ORDER BY clause not only can be used with the SELECT statement, but it also can be used with INSERT Statement and CREATE VIEW statement.
If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!
Learn More,
