(SQL Example for Citizen Data Scientist & Business Analyst)
SQL | ORDER BY
The ORDER BY statement in sql is used to sort the fetched data in either ascending or descending according to one or more columns.
- By default ORDER BY sorts the data in ascending order.
- We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.
Syntax of all ways of using ORDER BY is shown below:
- Sort according to one column: To sort in ascending or descending order we can use the keywords ASC or DESC respectively.
Syntax:SELECT * FROM table_name ORDER BY column_name ASC|DESC table_name: name of the table. column_name: name of the column according to which the data is needed to be arranged. ASC: to sort the data in ascending order. DESC: to sort the data in descending order. | : use either ASC or DESC to sort in ascending or descending order
- Sort according to multiple columns: To sort in ascending or descending order we can use the keywords ASC or DESC respectively. To sort according to multiple columns, separate the names of columns by (,) operator.
Syntax:SELECT * FROM table_name ORDER BY column1 ASC|DESC , column2 ASC|DESC
Queries:
- Sort according to single column: In this example we will fetch all data from the table Student and sort the result in descending order according to the column ROLL_NO.
SELECT * FROM Student ORDER BY ROLL_NO DESC;
Output:
ROLL_NO NAME ADDRESS PHONE Age 8 NIRAJ ALIPUR XXXXXXXXXX 19 7 ROHIT BALURGHAT XXXXXXXXXX 18 6 DHANRAJ BARABAJAR XXXXXXXXXX 20 5 SAPTARHI KOLKATA XXXXXXXXXX 19 4 DEEP RAMNAGAR XXXXXXXXXX 18 3 RIYANKA SILIGURI XXXXXXXXXX 20 2 PRATIK BIHAR XXXXXXXXXX 19 1 HARSH DELHI XXXXXXXXXX 18 To sort in ascending order we have to use ASC in place of DESC.
- Sort according to multiple columns:In this example we will fetch all data from the table Student and then sort the result in ascending order first according to the column Age. and then in descending order according to the column ROLL_NO.
SELECT * FROM Student ORDER BY Age ASC , ROLL_NO DESC;
Output:
ROLL_NO NAME ADDRESS PHONE Age 7 ROHIT BALURGHAT XXXXXXXXXX 18 4 DEEP RAMNAGAR XXXXXXXXXX 18 1 HARSH DELHI XXXXXXXXXX 18 8 NIRAJ ALIPUR XXXXXXXXXX 19 5 SAPTARHI KOLKATA XXXXXXXXXX 19 2 PRATIK BIHAR XXXXXXXXXX 19 6 DHANRAJ BARABAJAR XXXXXXXXXX 20 3 RIYANKA SILIGURI XXXXXXXXXX 20 In the above output you can see that first the result is sorted in ascending order according to Age.
There are multiple rows having same Age. Now, sorting further this result-set according to ROLL_NO will sort the rows with same Age according to ROLL_NO in descending order.
- Note that: ASC is the default value for ORDER BY clause. So, if you don’t specify anything after column name in ORDER BY clause, the output will be sorted in ascending order by default.Example: The following query will give similar output as the above:
SELECT * FROM Student ORDER BY Age , ROLL_NO DESC;
Output:
ROLL_NO NAME ADDRESS PHONE Age 7 ROHIT BALURGHAT XXXXXXXXXX 18 4 DEEP RAMNAGAR XXXXXXXXXX 18 1 HARSH DELHI XXXXXXXXXX 18 8 NIRAJ ALIPUR XXXXXXXXXX 19 5 SAPTARHI KOLKATA XXXXXXXXXX 19 2 PRATIK BIHAR XXXXXXXXXX 19 6 DHANRAJ BARABAJAR XXXXXXXXXX 20 3 RIYANKA SILIGURI XXXXXXXXXX 20
Excel formula for Beginners – How to Sort by one column in Excel
Learn to Code SQL Example – SQL | ORDER BY
Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.
Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners
Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:
All Notebooks in One Bundle: Data Science Recipes and Examples in Python & R.
End-to-End Python Machine Learning Recipes & Examples.
End-to-End R Machine Learning Recipes & Examples.
Applied Statistics with R for Beginners and Business Professionals
Data Science and Machine Learning Projects in Python: Tabular Data Analytics
Data Science and Machine Learning Projects in R: Tabular Data Analytics
Python Machine Learning & Data Science Recipes: Learn by Coding
R Machine Learning & Data Science Recipes: Learn by Coding
Comparing Different Machine Learning Algorithms in Python for Classification (FREE)
There are 2000+ End-to-End Python & R Notebooks are available to build Professional Portfolio as a Data Scientist and/or Machine Learning Specialist. All Notebooks are only $29.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.