Order By and Distinct Clause in Oracle #Diksha Oracle Training Centre

 

Order By and Distinct Clause in Oracle.

 

Today I will discuss about SQL Order by and Distinct Clause which is vastly used in SQL query to perform Operation in query. I suggest you to please see my previous blog for your reference

First start with Order By clause with Syntax and example.

Order by Clause: ORDER BY clause is used to sort the records in the result set for a SELECT statement. Order by clause is used to sort the result-set in ascending or descending order.

The Order By clause sorts the records in ascending order by default. To sort the records in descending order, use the DESC command in SQL.

The Syntax of Order by Clause in SQL is as below:

SELECT expression

FROM table_name

[WHERE conditions]

ORDER BY expression [ ASC | DESC ];

 

In Above syntax :

Expression: We need to specify the columns that will be retrieved or also we can use function for calculation to retrieve the column.

Table_name : Table is object which stored in the database , we get the records from the table . There should be at least  one table in the from clause so that we can retrieve the records from the table.

 

Where condition : Where is optional but if we want to filter the records against the requirement we can use where clause .

ASC :  In Order By ASC  sorts the result set in ascending order by given expression. It is also optional as by default all the records comes in ascending order if we use only order by clause.

DESC: In Order By DESC sorts the result in descending order by given expression. it is also optional but if we want to get the records in descending order we need to give DESC .

Note: If we only use order by clause then by default records comes in ascending order.

 

Let’s take an example of order by using both ASC and DESC in a table to see the records.

This example would return all records from the employee sorted by the name field in ascending. Below is the syntax to retrieve the data in ascending order.

SELECT *

FROM employee

ORDER BY emp_name ASC;

Here the name column is coming in ascending order. in Order by clause if we omit ASC then also we will get the same output as by default order by clause sorts the result in ascending order.

 

Let's take one example by using same table employee by using DESC in order by clause where we want to see the name in descending order .

SELECT *

FROM employee

ORDER BY emp_name DESC;

 

Here the name appears in Descending order as per our requirement.

Output of Above Queries is as below.

 

Let's use both ASC and DESC in order by clause , below the query for this applying in same table employee.

 

select * from employee

where age >17

ORDER BY

emp_id asc, emp_name desc;

 

Here we see records sorted by the employee_id   in ascending order, with a secondary sort by address in descending order.

 

Output of the above query is as below :

 

 

Let's Start with Distinct Clause in SQL with Syntax and examples.

DISTINCT CLAUSE :  In SQL DISTINCT clause is mainly used to remove duplicates from the result set from SELECT statement. In other word we can say we use distinct clause to see the filtered record which is not duplicate.

 Syntax of Distinct clause in SQL is as below:

 

 SELECT DISTINCT expression

FROM table_name

[WHERE conditions];

 In Above syntax :

Expression: We need to specify the columns that will be retrieved or also we can use function for calculation to retrieve the column.

Table_name : Table is object which stored in the database , we get the records from the table . There should be at least  one table in the from clause so that we can retrieve the records from the table.

Where condition : Where is optional but if we want to filter the records against the requirement we can use where clause .

Note : When we use only one expression in the DISTINCT clause, the query will return the unique values for that expression. However if we use more than one expression in the DISTINCT clause, the query will give unique combinations for the expressions in select statement.  DISTINCT clause doesn't ignore NULL values.

 

Let’s take one example of Distinct clause in employee table where in address we have some duplicate records see the below table .

Let’s find the unique designation from the employee table by using below syntax:

 

SELECT DISTINCT designation

FROM employee

ORDER BY designation;

Below is the output of above query.

 

Here we are seeing only 5 records as distinct by clause gives only unique value of designation in the table not the duplicate records.

 

Finding Unique values in multiple columns by using distinct clause in SQL.

 

let's look at how to use the SQL DISTINCT clause to remove duplicates from more than one field in a SELECT statement by using the same employee table .

 

SQL Query is as below :

 

SELECT DISTINCT  designation,emp_id

FROM employee

ORDER BY designation, emp_id;

 

Below is the output of above query.

 

This example would return each unique name and address combination. In this case, the DISTINCT applies to each field.

 

Note: DISTINCT clause also  consider NULL to be a unique value in SQL

 

This is all about Distinct and order by clause in SQL. You can use these clauses in complex query for your convenient and as per your requirement. This is very simple to use in queries and sometime you will see the benefits of using both the clause for seeing the query results in proper way to see the results of records. Please read this blog and let me know if you have any doubts. Please try these clauses in complex query.

 

Thanks.