SQL - Order By Clause

Ever thought of how can we arrange our columns in some particular order? Or thought of how to get random information?

If the answer to these questions is NO then this is the right article for you.

In this article, we'll be discussing,

When to use order by clause?
Syntax of order by clause
Sorting a column using order by
Getting random information using order by
Sorting multiple columns using order by

Let's begin.

When to use order by clause

Order by clause is used to sort the fetched data either in ascending or descending order. When we execute our select statement we get unsorted results as our output. In order to arrange them in a sorted way - order by comes in picture.

Syntax of Order by clause

SELECT * FROM table_name ORDER BY column_name

* = It is used for all columns in your table.
table_name = name of your table where the information is stored in form of rows and columns
Order By = Clause used to sort your column
column_name = Name of your column which you want to sort

By Default Order by sorts the column in ascending order. If you want to sort your column in descending order then you have to explicitly use the keyword "DESC".

Sorting a column using order by

Consider you have student table of a particular standard say 7th having columns as roll no, name, city, Marks

Roll_No	Name	State	Marks
101	Akanksha	Punjab	95
102	Isha	Haryana	80
103	Amit	MP	100
104	Rajat	UP	89
105	Amit	Punjab	97

This is our student of standard 7th data. Now we want to fetch student data in terms of their highest marks score.

Then,

SELECT NAME FROM STUDENT ORDER BY MARKS DESC;

Result

Roll_No	Name	State	Marks out of 100
103	Amit	MP	100
105	Amit	Punjab	97
101	Akanksha	Punjab	95
104	Rajat	UP	89
102	Isha	Haryana	80

Now, what if I want to sort by Names in ascending order but we are having the same names at two different rows then we can opt for Multiple columns order by.

This means first order by name ascending and if names are equal then go with roll no descending.

SELECT * FROM STUDENT ORDER BY NAME, ROLL_NO DESC;

Roll_No	Name	State	Marks out of 100
101	Akanksha	Punjab	95
105	Amit	Punjab	97
103	Amit	MP	100
102	Isha	Haryana	80
104	Rajat	UP	89

Some Practice Questions

We have a product table having columns as product_id, product_name, and category.

Product_id	Product_Name	Category
1	Apple	50
2	Mango	60
3	Apricot	45
4	Guava	50

Arrange the data in descending categories and ascending product_name.

2.Arrange the data in descending category, ascending product_name and ascending product_id.

Product_id	Product_Name	Category
1	Apple	70
2	Mango	50
3	Apricot	50
4	Apricot	50

If you face any difficulty in these two practice questions, either message me or comment below. I will try to solve your queries.

Getting random information using order by

If we want to get a random value from our table then we can use Rand() function for MySQL or dbms_random.value for Oracle. This can be useful if we are making some online testing app then we can fetch random questions from the database and so on.

Example

SELECT NAME FROM STUDENT ORDER BY dbms_random.value;

Result

Name

Isha

Amit

Rajat

Amit

Akanksha

You will get a random name.

You can practice the above question using order by random.

This article covers everything about Order By Clause. If you have any doubt or face any difficulty in any question which you practice you can reach out to me. I will be very happy to help.