Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can you order the result obtained by select query in MySQL?
It is common to select certain data or rows from a table. The rows are returned in the order in which they appear in the table. We may sometimes require that the rows we select from the table must be returned to us in ascending or descending order with respect to some column.
The “ORDER BY” statement is used to order the results with respect to some column. The following example will provide more clarity.
Suppose, we have a table which consists of various fields including the “name” field. We want to select all the rows from the table but we want the rows must be ordered in alphabetic order of the names. Here is where the “ORDER BY” statement comes into play. This scenario requires us to order the results in ascending order of the “name” field.
The “ORDER BY” statement ,by default, orders the specified column in ascending order. If you want the result to be ordered in descending order, you need to specify the same. To oder the result in descending order, the keyword “DESC” is to be specified.
Syntax
Ascending order
SELECT * FROM table_name ORDER BY column_name
Descending order
SELECT * FROM table_name ORDER BY column_name DESC
Steps invloved to order the data retrieved from a table using MySQL in python
import MySQL connector
establish connection with the connector using connect()
create the cursor object using cursor() method
create a query using the appropriate mysql statements
execute the SQL query using execute() method
close the connection
Suppose we have a table named “Student” as follows −
+----------+---------+-----------+------------+ | Name | Class | City | Marks | +----------+---------+-----------+------------+ | Karan | 4 | Amritsar | 95 | | Sahil | 6 | Amritsar | 93 | | Kriti | 3 | Batala | 88 | | Khushi | 9 | Delhi | 90 | | Kirat | 5 | Delhi | 85 | +----------+---------+-----------+------------+
Example
We want to select all the rows from the table but in alphabetic order of their names. In short, we want to order the result in ascending order of names.
import mysql.connector db=mysql.connector.connect(host="your host", user="your username", password="your password",database="database_name") cursor=db.cursor() query= "SELECT * FROM Students ORDER BY Name" cursor.execute(query) for row in cursor: print(row)
The above code when executed succesfully returns the rows in ascending or alphabetic order of the names of the students.
Output
(‘Amit’ , 9 , ‘Delhi’ , 90) (‘Karan’, 4 ,’Amritsar’ , 95) (‘Kriti’ , 3 , ‘Batala’ ,88) (‘Priya’ , 5 , ‘Delhi’ ,85) (‘Sahil’ , 6 , ‘Amritsar’ ,93)
All the rows displayed are in alphabetic order of the names. Similarly, the rows could have been arranged in ascending or descending order of the marks following the similar syntax.
568 Views
