Sorting records via SQL query in Python
To sort the result lines, you need to use the command ORDER
BY.
Example
Let's select users all users from our table and sort them by age from smallest to largest:
query = "SELECT * FROM users ORDER BY age"
<-show-
The result of the executed code:
<~show~>{'id': 1, 'name': 'user1', 'age': 23, 'salary': 400}
{'id': 3, 'name': 'user3', 'age': 23, 'salary': 500}
{'id': 2, 'name': 'user2', 'age': 25, 'salary': 500}
{'id': 5, 'name': 'user5', 'age': 27, 'salary': 500}
{'id': 6, 'name': 'user6', 'age': 28, 'salary': 900}
{'id': 4, 'name': 'user4', 'age': 30, 'salary': 900}
Example
Let's change the sort order using the DESC command:
query = "SELECT * FROM users ORDER BY age DESC"
The result of the executed code:
{'id': 4, 'name': 'user4', 'age': 30, 'salary': 900}
{'id': 6, 'name': 'user6', 'age': 28, 'salary': 900}
{'id': 5, 'name': 'user5', 'age': 27, 'salary': 500}
{'id': 2, 'name': 'user2', 'age': 25, 'salary': 500}
{'id': 1, 'name': 'user1', 'age': 23, 'salary': 400}
{'id': 3, 'name': 'user3', 'age': 23, 'salary': 500}
Example
Let's select all users with a salary of 500 and sort them by age from lowest to highest:
query = "SELECT * FROM users WHERE salary=500 ORDER BY age"
The result of the executed code:
{'id': 3, 'name': 'user3', 'age': 23, 'salary': 500}
{'id': 2, 'name': 'user2', 'age': 25, 'salary': 500}
{'id': 5, 'name': 'user5', 'age': 27, 'salary': 500}
Example
You can sort not by one field, but by several. Let's select all users for example and sort them first by increasing age, and then sort users with the same age by increasing salary:
query = "SELECT * FROM users ORDER BY age, salary"
The result of the executed code:
{'id': 1, 'name': 'user1', 'age': 23, 'salary': 400}
{'id': 3, 'name': 'user3', 'age': 23, 'salary': 500}
{'id': 2, 'name': 'user2', 'age': 25, 'salary': 500}
{'id': 5, 'name': 'user5', 'age': 27, 'salary': 500}
{'id': 6, 'name': 'user6', 'age': 28, 'salary': 900}
{'id': 4, 'name': 'user4', 'age': 30, 'salary': 900}
Example
The ORDER BY command can be combined with LIMIT . In this case, you first need to write the sort command, and then the limit. In the following example, we first sort the records by increasing age, and then take the first 3 pieces:
query = "SELECT * FROM users ORDER BY age LIMIT 3"
The result of the executed code:
{'id': 1, 'name': 'user1', 'age': 23, 'salary': 400}
{'id': 3, 'name': 'user3', 'age': 23, 'salary': 500}
{'id': 2, 'name': 'user2', 'age': 25, 'salary': 500}
Practical tasks
Get all users and sort them by increasing salary.
Get all users and sort them by salary in descending order.
Get all users and sort them by name.
Get users with a salary of 500 and sort them by age.
Get all users and sort them by name and salary.
Sort users by salary ascending and get the first 3 employees from the sorting result.
Sort users by salary descending and get the first 3 users from the sorting result.
