In my earlier post I showed how to use order by clause , now in this post I will go bit more details on how to sort by columns in SQL queries.
[ORDER BY COLUMN [ , COLUMN ]…]
In the above syntax we can also use the alias, then the syntax will be like as below:
FROM …< Your Table> [alias]
[ORDER BY [alias.]COLUMN [ , [alias.]COLUMN ]…]
I am using Oracle Release 126.96.36.199.0 XE version where I have installed SCOTT schema to show the examples.
Here is the first example for sorting data by column :
SQL> select empno, ename, hiredate from emp order by hiredate;
The above image shows the result when we execute the SQL.
What is actually does ? So before going there let us first see what we get when we only run the SQL without order by clause .
Here you can see the hiredate column is not sorted because we have not used the order by clause here.
So based on the order by clause it orders all the records that are been selected based on the hiredate column. The important part here is we can
see that when we use the order by clause then it get sorted but in ascending order by default.
Now we can also use the order by clause without giving it on the select clause for example, if we run the below SQL:
SQL> select empno, ename, hiredate from emp order by deptno;
From the above SQL you can see that we haven’t used the deptno on the select part but we used it on the order by clause, which means the selected records are been sorted based on the department number. So if we use the deptno on the select statement then the result will be like below:
SQL> select empno, ename, hiredate,deptno from emp order by deptno;
Now you can see the the deptno is been sorted but in ascending order along with the other columns from employee table. You can also see that the deptno is duplicated so match on the empno is not on ascending order. Now we can also use multiple columns on a single order by clause like :
SQL> select empno, ename, hiredate,deptno from emp order by deptno, empno;
You can see the difference on the department number ‘20’ where in my earlier query it only sorts on the deptno where the empno was not sorted and now as I have added the empno on the order by clause so first the records are been sorted by deptno and then again it sorts on empno.
We can use the order by clause by using the alias on the table, for example if we are on another schema .. lets take SYS user and I want to use EMP table from SCOTT user then I would write my query like as below:
SQL> select * from scott.emp order by ename;
Another way to use it by alias on the column name if we are using alias on the table at first like :
SQL> select * from scott.emp a order by ename;
Then we will also get the same result but it is always advisable to use the alias on the column if we have used it on the table. Not using the alias could cause a problem if we are having multiple tables on the select statement and we are using a order by clause.
SQL> select * from scott.emp a, SCOTT.dept b order by deptno;
You can see that we are getting the error for column ambiguously defined.
To make use of this query we should use the column alias as like below:
SQL> select * from scott.emp a, SCOTT.dept b order by b.deptno;
For more information you can go through Oracle documentation .