The keyword ‘ORDER BY‘ clause used in SQL to order the data sets retrieved from a SQL database.
The sorting of result set can be based on column or columns.
Example:
Step:1: Create the table as follows:
SQL> create table MYTABLE (NAME varchar2(50), Age number,id number);< Table created SQL>
STEP:2: Insert few records into ‘MYTABLE’.
SQL> insert into MYTABLE values('BBB',22,1);
SQL> insert into MYTABLE values('DDD',22,2);
SQL> insert into MYTABLE values('CCC',22,3);
SQL> insert into MYTABLE values('AAA',22,4);
SQL> insert into MYTABLE values('EEE',22,5);
STEP:3: Run a normal query to retrieve the result set.
SQL> select * from MYTABLE; NAME AGE ID BBB 22 1< DDD 22 2 CCC 22 3 AAA 22 4 EEE 22 5
STEP:4: Now use the ‘ORDER BY‘ keyword in the query and use ‘NAME’ column to sort.
SQL> select * from MYTABLE order by name; NAME AGE ID AAA 22 4 BBB 22 1 CCC 22 3 DDD 22 2 EEE 22 5
By defaults the ‘ORDER BY‘ query returns natural ordered results.Otherwise you need to use ‘ASC‘ or ‘DESC‘ keywords.
ORDER BY clause usually followed by ‘ASC‘ or ‘DESC‘ keywords in order to indicate the sorting order either by ‘ascending’ or ‘descending’.
ASC keyword orders the result set by the specified columns alphabetically. DESC sort the result set backwards ,i.e just opposite of ‘ASC’
STEP:5: Use the ‘DESC‘ keyword in the query mentioned in STEP:4
SQL> select * from MYTABLE order by name DESC; NAME AGE ID EEE 22 5 DDD 22 2 CCC 22 3 BBB 22 1 AAA 22 4
You can sort your result set by more than one column by specifying those columns in the SQL ORDER BY list.When using ORDER BY with more than one column, you need to separate the columns following ORDER BY with commas.
If you need specific ordering based on more than one column, you need to specify ASC and/or DESC after each column.
STEP:6: Example of using multiple columns in ‘ORDER BY’ clause.
SQL> select * from MYTABLE order by name ASC,ID DESC; NAME AGE ID AAA 22 4 BBB 22 1 CCC 22 3 DDD 22 2 EEE 22 5
Related Articles,
- SQL MIN Function | MIN() function in SQL
- SQL COUNT() Function ,Usage of COUNT(*) and COUNT(1)
- Oracle Max(Date)? | sql max date
- Oracle Trim Function | PL/SQL TRIM Function.
- Oracle Decode Function | Use PLSQL DECODE function to handle NULL and default values
- SQL: MAX Function | SQL Max examples
- SQL GROUP BY Statement | SQL GROUP BY Clause examples
- NVL | Oracle/PLSQL: NVL Function
Technorati Tags:
ORDER BY, SQL ORDER BY, SQL Keywords, ASC, DESC