The ORDER BY clause is used in a SELECT statement to sort results either in ascending or descending order. Oracle sorts query results in ascending order by default.
Syntax for using SQL ORDER BY clause to sort data is:
How to use expressions in the ORDER BY Clause?
The ORDER BY clause allows you to sort the records in your result set. The ORDER BY clause can only be used in SELECT statements.
The syntax for the ORDER BY clause is:
SELECT columns
FROM tables
WHERE predicates
ORDER BY column ASC/DESC;
The ORDER BY clause sorts the result set based on the columns specified. If the ASC or DESC value is omitted, it is sorted by ASC.
ASC indicates ascending order. (default)
DESC indicates descending order.
FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY supplier_city;
This would return all records sorted by the supplier_city field in ascending order.
FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY supplier_city DESC;
This would return all records sorted by the supplier_city field in descending order.
SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY 1 DESC;
This would return all records sorted by the supplier_city field in descending order, since the supplier_city field is in position #1 in the result set.
FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY supplier_city DESC, supplier_state ASC;
This would return all records sorted by the supplier_city field in descending order, with a secondary sort by supplier_state in ascending order.
Syntax for using SQL ORDER BY clause to sort data is:
SELECT column-list
FROM table_name [WHERE condition]
[ORDER BY column1 [, column2, .. columnN] [DESC]];
FROM table_name [WHERE condition]
[ORDER BY column1 [, column2, .. columnN] [DESC]];
database table "employee";
id | name | dept | age | salary | location |
100 | Ramesh | Electrical | 24 | 25000 | Bangalore |
101 | Hrithik | Electronics | 28 | 35000 | Bangalore |
102 | Harsha | Aeronautics | 28 | 35000 | Mysore |
103 | Soumya | Electronics | 22 | 20000 | Bangalore |
104 | Priya | InfoTech | 25 | 30000 | Mangalore |
For Example: If you want to sort the employee table by salary of the employee, the sql query would be.
SELECT name, salary FROM employee ORDER BY salary;
The output would be like
name | salary |
---------- | ---------- |
Soumya | 20000 |
Ramesh | 25000 |
Priya | 30000 |
Hrithik | 35000 |
Harsha | 35000 |
The query first sorts the result according to name and then displays it.
You can also use more than one column in the ORDER BY clause.
If you want to sort the employee table by the name and salary, the query would be like,
SELECT name, salary FROM employee ORDER BY name, salary;
The output would be like:
name | salary |
------------- | ------------- |
Soumya | 20000 |
Ramesh | 25000 |
Priya | 30000 |
Harsha | 35000 |
Hrithik | 35000 |
NOTE:The columns specified in ORDER BY clause should be one of the columns selected in the SELECT column list.
You can represent the columns in the ORDER BY clause by specifying the position of a column in the SELECT list, instead of writing the column name.
The above query can also be written as given below,
SELECT name, salary FROM employee ORDER BY 1, 2;
By default, the ORDER BY Clause sorts data in ascending order. If you want to sort the data in descending order, you must explicitly specify it as shown below.
SELECT name, salary
FROM employee
ORDER BY name, salary DESC;
FROM employee
ORDER BY name, salary DESC;
The above query sorts only the column 'salary' in descending order and the column 'name' by ascending order.
If you want to select both name and salary in descending order, the query would be as given below.
SELECT name, salary
FROM employee
ORDER BY name DESC, salary DESC;
FROM employee
ORDER BY name DESC, salary DESC;
How to use expressions in the ORDER BY Clause?
Expressions in the ORDER BY clause of a SELECT statement.
For example: If you want to display employee name, current salary, and a 20% increase in the salary for only those employees for whom the percentage increase in salary is greater than 30000 and in descending order of the increased price, the SELECT statement can be written as shown below
SELECT name, salary, salary*1.2 AS new_salary
FROM employee
WHERE salary*1.2 > 30000
ORDER BY new_salary DESC;
FROM employee
WHERE salary*1.2 > 30000
ORDER BY new_salary DESC;
The output for the above query is as follows.
name | salary | new_salary |
---------- | ---------- | ------------- |
Hrithik | 35000 | 37000 |
Harsha | 35000 | 37000 |
Priya | 30000 | 36000 |
NOTE:Aliases defined in the SELECT Statement can be used in ORDER BY Clause.
-------------------------------------------------------------------------------------------------
The syntax for the ORDER BY clause is:
SELECT columns
FROM tables
WHERE predicates
ORDER BY column ASC/DESC;
The ORDER BY clause sorts the result set based on the columns specified. If the ASC or DESC value is omitted, it is sorted by ASC.
ASC indicates ascending order. (default)
DESC indicates descending order.
Example #1
SELECT supplier_cityFROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY supplier_city;
This would return all records sorted by the supplier_city field in ascending order.
Example #2
SELECT supplier_cityFROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY supplier_city DESC;
This would return all records sorted by the supplier_city field in descending order.
Example #3
You can also sort by relative position in the result set, where the first field in the result set is 1. The next field is 2, and so on.SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY 1 DESC;
This would return all records sorted by the supplier_city field in descending order, since the supplier_city field is in position #1 in the result set.
Example #4
SELECT supplier_city, supplier_stateFROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY supplier_city DESC, supplier_state ASC;
This would return all records sorted by the supplier_city field in descending order, with a secondary sort by supplier_state in ascending order.
No comments:
Post a Comment