Order by Clause

In Oracle there is a way to order the data in a sequence or reorganize the information that is been pulled from Oracle database. To short the data we can use “ORDER BY EXPRESSION”.

Syntax for Order by Clause:

SELECT …
FROM ..
[WHERE ..]
ORDER BY { COLUMN |EXPRESSION| POSITION }[  ,….] } }
    [ ASC | DESC ]
    [ NULLS FIRST | NULLS LAST ]    ]

 

In the above syntax you can see the square brackets which means they are optional (the “WHERE” condition and the “ORDER BY”).

In the syntax its says that we can use the order by clause using the column or an expression which can be a function or the sum of a column or we can use a position i.e. a number that is there in the select list . For example if we have three columns like :

SQL> SELECT COLUMN1 , COLUMN2 , COLUMN3 ..FROM << Your Table >>

Then the position will be 1 for column1 , 2 for column2 and 3 for column3 . Similarly we use the ascending or descending order after the column or expression or the position selections.

The order by clause is always applied at last of the query or a subquery .There is a difference in where clause and order by clause .

The “WHERE” clause reduces the query and do not pull the data across the network, which internally reduces the I/O . Now when we use the “ORDER BY” clause it takes the data which is been pulled using a the “WHERE” clause or without it and resorts it. It does the resorting on the server itself , so for example if the where clause pulls 100 records out of 100k records then the order by clause will sort the data only on 100 records not on 100k records.

Note: In the order by clause we can also use the column alias which is again optional (alias.column).

Example:

SQL> select object_name from all_objects where rownum <=10 order by object_name ;

OBJECT_NAME
——————————
CON$
C_COBJ#
ICOL$
I_CDEF2
I_IND1
I_OBJ#
I_OBJ5
I_USER1
PROXY_ROLE_DATA$
UNDO$

10 rows selected.

From the above example you can see that the where condition only selected 10 rows from the table and then it sorts on the selected rows of the data.

 

 

To know more information on syntax visit : http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#i2065646

 

 

 

Advertisements

3 thoughts on “Order by Clause

    • I don’t have any solution for hackers, because I believe that they are very intelligent to crack anything what you make,if you notice there will always be a new patch or release on any antivirus (Means there is always some back to full proof concept to protect).

      You should check with some antivirus company to suggest you the best. And as per me I take a backup of my blog or any post I make.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s