sql order by asc and desc

When we query a database, the results we get back may not always be in the order we want them to be. That’s where the ORDER BY clause comes in handy. The ORDER BY clause is used to sort the result set in either ascending or descending order. In this article, we’ll cover the syntax for the ORDER BY clause and give some examples of how it can be used.

If you’re new to SQL, you may want to first check out our article on SQL Select to learn more about retrieving data from a database table. Additionally, the SQL WHERE with AND, OR, NOT Operators article can provide useful information on filtering data based on specific conditions.

Syntax

The syntax for the ORDER BY clause is as follows:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

The ORDER BY clause is used after the FROM clause and before any GROUP BY or HAVING clauses. You specify the column or columns you want to sort by, and you can specify whether you want the sort order to be ascending (ASC) or descending (DESC).

If you don’t specify a sort order for a column, the default sort order is ascending. You can sort by multiple columns by specifying them in the ORDER BY clause separated by commas.

Examples

Let’s take a look at some examples of how the ORDER BY clause can be used.

Suppose we have a table called employees with the following data:

idnameagesalary
1Alice2560000
2Bob3070000
3Charlie3580000
4Dave4090000
5Eve45100000

If we want to retrieve all the employees from the table and sort them by salary in descending order, we can use the following SQL query:

SELECT * FROM employees ORDER BY salary DESC;

This would return the following result set:

idnameagesalary
5Eve45100000
4Dave4090000
3Charlie3580000
2Bob3070000
1Alice2560000

Notice that the result set is sorted by salary in descending order, from highest to lowest.

We can also sort by multiple columns. For example, if we want to retrieve all the employees from the table and sort them first by age in ascending order, and then by salary in descending order, we can use the following SQL query:

SELECT * FROM employees ORDER BY age ASC, salary DESC;

This would return the following result set:

idnameagesalary
1Alice2560000
2Bob3070000
3Charlie3580000
4Dave4090000
5Eve45100000

Notice that the result set is first sorted by age in ascending order, and then by salary in descending order.

Practice tasks

DateCategoryDescriptionAmount
2023-04-01HousingRent payment-1000
2023-04-05FoodGroceries-200
2023-04-09TransportGas for car-50
2023-04-15FoodRestaurant dinner-100
2023-04-20HousingHome insurance-150
2023-04-25UtilitiesElectricity bill-80
2023-04-29FoodGroceries-150
2023-05-01IncomeSalary2000
2023-05-03FoodGroceries-100
2023-05-06ShoppingClothes shopping-300
2023-05-10UtilitiesWater bill-50
2023-05-15HousingRent payment-1000
2023-05-20HousingHome insurance-150
2023-05-25UtilitiesInternet bill-100
2023-05-30FoodRestaurant lunch-50

Beginner

  1. Write a SQL query that retrieves all rows from the personal finance table and sorts them by date in ascending order.
  2. Write a SQL query that retrieves all rows from the personal finance table and sorts them by amount in descending order.

Intermediate

  1. Write a SQL query that retrieves all rows from the personal finance table and sorts them by category in ascending order, and then by date in descending order.
  2. Write a SQL query that retrieves all rows from the personal finance table and sorts them by amount in ascending order, and then by date in ascending order.

Advanced

  1. Write a SQL query that retrieves the total amount spent on food in April, grouped by day.
  2. Write a SQL query that retrieves the total amount spent on each category in May, sorted by the total amount in descending order.

Conclusion

The ORDER BY clause is a powerful SQL feature that enables you to sort your query results in a specific order. In this article, we covered the syntax for the ORDER BY clause and provided examples of how it can be used to sort data in ascending or descending order based on one or more columns.

By mastering the ORDER BY clause, you can become a more efficient and effective data analyst, as sorting your query results can help you make sense of your data and make informed decisions. To further enhance your SQL skills, we recommend checking out our articles on SQL Insert Into, SQL Update, and SQL Delete Statements.

Similar Posts

Leave a Reply

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