
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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.
- Related Articles
- MySQL query to select distinct order by id
- How to get MySQL query result in same order as given by IN clause?
- MySQL query to ORDER BY records on the basis of modulus result
- How to order and select query with conditions in MySQL?
- How can I display MySQL query result vertically?
- MySQL query to get result from multiple select statements?
- Order MySQL query by multiple ids?
- How to order results of a query randomly & select random rows in MySQL?
- MySQL query to select rows except first row in descending order?
- MySQL query to order by NULL values
- How to ORDER BY FIELD with GROUP BY in a single MySQL query?
- Can we order a MySQL result with mathematical operations?\n
- How to select an empty result set in MySQL?
- Can we use SELECT NULL statement in a MySQL query?
- How can we divide the result set returned by MySQL into groups?
