SQL for QA: Tutorial#6 – ORDER BY Clause

In SQL, ORDER BY clause is used to sort the records of the table. It is used with SELECT statement.

Using this clause, we can sort the data by Ascending or Descending order.

By default, ORDER BY clause sort the data in Ascending order, for Descending order we need to write down DESC keyword explicitly.

We can apply for Order By clause on multiple columns also.

Syntax:

Let’s take a sample table Employee:

 

Example 1:  Use of ORDER BY clause.

Table sorted as per Name column. By default it sorts the data in ascending order of employee name.

OUTPUT:

Example 2:   By using DESC keyword

OUTPUT is displayed in Descending Sorted order by Name column.

OUTPUT:

Example 3: Order by clause with Multiple Columns

The given SQL statement, the table get sorted on the basis of 2 columns. First, it sorts and displays all the department and then under the departments its employee get displayed in sorted order.

First, it will get sorted by Department and then under the Department by Name

SELECT Department, Name FROM Employee ORDER BY Department, Name;

OUTPUT

Example 4: ORDER BY with Column Position

We can write the columns in the ORDER BY clause by specifying the position of a column in the SELECT clause, instead of writing the column name.

We can write above query using the position number of the column.

OUTPUT:

If you check the above results you can clearly see that the rows are sorted on the first column i.e. Department and withing that group of rows of the same department, they are sorted based on Name.

Example 5: Use of Expressions in the ORDER BY Clause.

Aliases used in the SELECT Statement can be used in ORDER BY Clause.

OUTPUT:

Example 6: Use of ASC and DESC keyword at the same time

Here we are applying ASC and DESC keyword at a time in a query.

Displaying departments in Ascending order and the employees under the departments in Descending order.

SELECT Department, Name FROM Employee ORDER BY Department ASC, Name DESC;

OUTPUT:

In the next article of this series, we will see GROUP BY clause.

Tutorial Index:

  1. SQL For QA : Tutorial#1 – Select Query
  2. SQL For QA : Tutorial#2 – Where Clause
  3. SQL for QA: Tutorial#3 – INSERT INTO Statement
  4. SQL for QA: Tutorial#4 – UPDATE Statement
  5. SQL for QA: Tutorial#5 – DELETE Statement
  6. SQL for QA: Tutorial#6 – ORDER BY Clause
  7. SQL for QA: Tutorial#7 – GROUP BY Clause
  8. SQL for QA: Tutorial#8 – UNION Clause
  9. SQL for QA: Tutorial#9 – JOIN Clause

Add a Comment

Your email address will not be published. Required fields are marked *