Example of sorting results in MySQL

Hi,

How to sort the results in MYSQL query?

Thanks

View Answers

July 12, 2016 at 8:44 PM

Hi,

You can use the order by clause in SQL statement to sort the data in ascending or descending orders.

Lets consider the following table:

CREATE TABLE employee (              
  id int(10),         
  emp_name varchar(60),   
  dob date 
  );

Following query for entering the data:

insert into employee values(01,'Ashok','1981-12-20');
insert into employee values(02,'Kamlesh','1982-12-21');
insert into employee values(03,'Vishal','1981-12-21');
insert into employee values(04,'Sumit','2008-12-20');
insert into employee values(02,'Kim','1981-12-23');
insert into employee values(02,'Dinesh','1981-12-24');
insert into employee values(02,'Rajeev','2001-12-25');
insert into employee values(01,'John',' 2000-12-03');

Now you can get the data sorted by id using following query:

select * from employee order by id asc;

Following is other examples of sorting:

mysql> select * from employee order by id;
+------+----------+------------+
| id   | emp_name | dob        |
+------+----------+------------+
|    1 | Ashok    | 1981-12-20 |
|    1 | John     | 2000-12-03 |
|    2 | Kamlesh  | 1982-12-21 |
|    2 | Kim      | 1981-12-23 |
|    2 | Dinesh   | 1981-12-24 |
|    2 | Rajeev   | 2001-12-25 |
|    3 | Vishal   | 1981-12-21 |
|    4 | Sumit    | 2008-12-20 |
+------+----------+------------+
8 rows in set (0.00 sec)

mysql> select * from employee order by id asc;
+------+----------+------------+
| id   | emp_name | dob        |
+------+----------+------------+
|    1 | Ashok    | 1981-12-20 |
|    1 | John     | 2000-12-03 |
|    2 | Kamlesh  | 1982-12-21 |
|    2 | Kim      | 1981-12-23 |
|    2 | Dinesh   | 1981-12-24 |
|    2 | Rajeev   | 2001-12-25 |
|    3 | Vishal   | 1981-12-21 |
|    4 | Sumit    | 2008-12-20 |
+------+----------+------------+
8 rows in set (0.01 sec)

mysql> select * from employee order by id desc;
+------+----------+------------+
| id   | emp_name | dob        |
+------+----------+------------+
|    4 | Sumit    | 2008-12-20 |
|    3 | Vishal   | 1981-12-21 |
|    2 | Kamlesh  | 1982-12-21 |
|    2 | Kim      | 1981-12-23 |
|    2 | Dinesh   | 1981-12-24 |
|    2 | Rajeev   | 2001-12-25 |
|    1 | Ashok    | 1981-12-20 |
|    1 | John     | 2000-12-03 |
+------+----------+------------+
8 rows in set (0.00 sec)

mysql> select * from employee order by emp_name desc;
+------+----------+------------+
| id   | emp_name | dob        |
+------+----------+------------+
|    3 | Vishal   | 1981-12-21 |
|    4 | Sumit    | 2008-12-20 |
|    2 | Rajeev   | 2001-12-25 |
|    2 | Kim      | 1981-12-23 |
|    2 | Kamlesh  | 1982-12-21 |
|    1 | John     | 2000-12-03 |
|    2 | Dinesh   | 1981-12-24 |
|    1 | Ashok    | 1981-12-20 |
+------+----------+------------+
8 rows in set (0.00 sec)

mysql> select * from employee order by emp_name asc;
+------+----------+------------+
| id   | emp_name | dob        |
+------+----------+------------+
|    1 | Ashok    | 1981-12-20 |
|    2 | Dinesh   | 1981-12-24 |
|    1 | John     | 2000-12-03 |
|    2 | Kamlesh  | 1982-12-21 |
|    2 | Kim      | 1981-12-23 |
|    2 | Rajeev   | 2001-12-25 |
|    4 | Sumit    | 2008-12-20 |
|    3 | Vishal   | 1981-12-21 |
+------+----------+------------+
8 rows in set (0.00 sec)


mysql> select * from employee order by id,emp_name asc;
+------+----------+------------+
| id   | emp_name | dob        |
+------+----------+------------+
|    1 | Ashok    | 1981-12-20 |
|    1 | John     | 2000-12-03 |
|    2 | Dinesh   | 1981-12-24 |
|    2 | Kamlesh  | 1982-12-21 |
|    2 | Kim      | 1981-12-23 |
|    2 | Rajeev   | 2001-12-25 |
|    3 | Vishal   | 1981-12-21 |
|    4 | Sumit    | 2008-12-20 |
+------+----------+------------+
8 rows in set (0.00 sec)

mysql> select * from employee order by id,emp_name asc;
+------+----------+------------+
| id   | emp_name | dob        |
+------+----------+------------+
|    1 | Ashok    | 1981-12-20 |
|    1 | John     | 2000-12-03 |
|    2 | Dinesh   | 1981-12-24 |
|    2 | Kamlesh  | 1982-12-21 |
|    2 | Kim      | 1981-12-23 |
|    2 | Rajeev   | 2001-12-25 |
|    3 | Vishal   | 1981-12-21 |
|    4 | Sumit    | 2008-12-20 |
+------+----------+------------+
8 rows in set (0.00 sec)

mysql> select * from employee order by id,emp_name desc;
+------+----------+------------+
| id   | emp_name | dob        |
+------+----------+------------+
|    1 | John     | 2000-12-03 |
|    1 | Ashok    | 1981-12-20 |
|    2 | Rajeev   | 2001-12-25 |
|    2 | Kim      | 1981-12-23 |
|    2 | Kamlesh  | 1982-12-21 |
|    2 | Dinesh   | 1981-12-24 |
|    3 | Vishal   | 1981-12-21 |
|    4 | Sumit    | 2008-12-20 |
+------+----------+------------+
8 rows in set (0.00 sec)

mysql> select * from employee order by id,emp_name asc;
+------+----------+------------+
| id   | emp_name | dob        |
+------+----------+------------+
|    1 | Ashok    | 1981-12-20 |
|    1 | John     | 2000-12-03 |
|    2 | Dinesh   | 1981-12-24 |
|    2 | Kamlesh  | 1982-12-21 |
|    2 | Kim      | 1981-12-23 |
|    2 | Rajeev   | 2001-12-25 |
|    3 | Vishal   | 1981-12-21 |
|    4 | Sumit    | 2008-12-20 |
+------+----------+------------+
8 rows in set (0.00 sec)

Thanks


July 18, 2016 at 7:45 AM

Check following video:

Thanks









Related Tutorials/Questions & Answers:
Example of sorting results in MySQL
Example of sorting results in MySQL  Hi, How to sort the results in MYSQL query? Thanks   Hi, You can use the order by clause in SQL... is other examples of sorting: mysql> select * from employee order by id
MySql Databse query to fetch results from database
MySql Databse query to fetch results from database  Hi. I have a field in database named stages. its datatype is varchar(60). It contains values chennai,trichy,kanchipuram for a single record. I have to retrieve these data from
Advertisements
sorting
sorting   write a program to arrange sorting in rows,column and diagonal
Sorting
Sorting  can any help me know which sorting algorithm java uses for sorting collection and arrays
sorting
sorting  write to a program to arrange the sorting in rows, column and digonal
sorting
sorting  how to do sorting without using bubble sort,selection sort
MySql Databse query to fetch results from database and display it in HTML File
MySql Databse query to fetch results from database and display it in HTML File  Hi. I have a field in database named stages. its datatype is varchar(60). It contains values chennai,trichy,kanchipuram for a single record. I have
Order by example in MySQL
Order by example in MySQL  Hi, How to use the order by in MySQL? Thanks   Hi, The order by clause in used in MySQL for ordering the result in ascending or descending order. For example following query order the data
Distinct and Limit example in MySQL
Distinct and Limit example in MySQL  Hi, How to use the distinct and limit with SQL query in MySQL? Thanks   Hi, Here is example...) from email where email_count >100; Here is example of limit in MySQL
php mysql fetch array example
php mysql fetch array example  How to fetch data from mysql and store it into an array in PHP? Please post an example. Thanks in Advance
Sorting in Java
Sorting in Java  Sorting in Java
J2ME RMS Sorting Example
J2ME RMS Sorting Example       This example simply shows how to use of RMS package. In this example we are going to sort the specified string by the implementing of RecordComparator
login page php mysql example
login page php mysql example  How to create a login page for user in PHP Mysql? Please provide me a complete tutorial. Thanks in Advance
example of before insert trigger in mysql
example of before insert trigger in mysql  Hi, How to create a simple before insert trigger in MySQL? Thanks   Hi, The before insert... in MySQL database server and executed on the server itself. So, its very fast. First
mysql select into table from another table example
mysql select into table from another table example  Can you suggest the correct example of select into table from another table example in MySQL... from one table into another table. Check the example at MySQLselect into new
MySQL take backup of a table - example code needed
MySQL take backup of a table - example code needed  How to take backup of a table in MySQL? I have a database which contains many tables. What is the command to take the backup of one table with data from database? Thanks
MYSQL - mysql copy table to another table example by creating new table
MYSQL - mysql copy table to another table example by creating new table  Hi, I have a table with many fields and hue data is in it. I want to create... the query: create table <newtable> select * from <old table>; Example
Hibernate example step by step in Eclipse with MySQL
is using Eclipse IDE and the example program connects to MySQL Database. Check...Hibernate example step by step in Eclipse with MySQL  Hi, I am total... and looking for any good Hibernate example for learning step by step in Eclipse
linear sorting
linear sorting  what is linear sorting? can any send me an example   Hi Friend, Try this: import java.util.*; class ArraySort{ public...("After Sorting: "); for(int i=0;i<arr.length;i++){ System.out.println
group by sorting
group by sorting  group by sorting of data in SQL
What is Sorting in PHP
What is sorting Sorting is a process of arranging the elements of an array... example discussed selection sort using 'c'-language: main()ADS_TO_REPLACE_1...; printf("\n After sorting the values are:");     
Example of Average, Sum, Min and Max functions in MySQL
, Here is few example of aggregate functions in MySQL: mysql> select * from...Example of Average, Sum, Min and Max functions in MySQL  Hi, How to use the aggregate functions like Average, Sum, Min and Max in MySQL? Thanks
Results
Results After action executes business logic, <result > tag is used to display the View. Struts2 provides or supports to 11 types of Results:ADS... in web application. There are also other ways to render results: Result type
Sorting the array
Sorting the array  Implement a program to process votes for 5 candidates in a talent contest. The program should use a String array to hold the names...); System.out.println("Enter vote number of the candidate results: "); int
sorting an array of string with duplicate values - Java Beginners
sorting an array of string  Example to sort array string
MySQL Not in Example
MySQL Not in Example       MySQL Not in is used to set the update of  records for only... with ExampleADS_TO_REPLACE_1 The Tutorial shows you an example from MySQL
Mysql Last
_TO_REPLACE_1 The Section of the Tutorial provides you an example from 'Mysql Last... Mysql Last       Mysql Last is used TO limit Mysql query result that fall in a specified
MySQL allowMultiQueries JSP Example
MySQL allowMultiQueries JSP Example In this section we will discuss about how to run multiple sql queries in Java. This example is given here.... This example explains you all the steps for allowing multiple queries using MySQL
Java Hashmap Sorting - Java Beginners
Java Hashmap Sorting   I have a sorting issue with a Hashmap. My... a database query and place the results in a Hashmap. When I iterate thru the Hashmap, it loses the original alphabetical sorting done by the database. So, my problem
MySQL Dump Example
periodically or after certain period of time. MySQL provides several techniques... will study about MySQL dump and it's procedures to store the data. You... about how to store the data using MySQL dump. We are assuming that you already
Spring 3 MVC Login Form Example with Database MySql
Spring 3 MVC Login Form Example with Database MySql  Sir i have checked your project of Spring 3 MVC Login Form Example But Sir Not able to do It with database Mysql. Can you Provide code for login with database. Thanks
sorting and storing data
sorting and storing data   sorting and storing data in UITableView
Sorting Program
Sorting Program  To sort 10items in alphabetical order   Arrays.sort(name of ur array)   import java.util.*; class ArrayExample{ public static void main(String[] args) { String array[]=new
sorting numbers
sorting numbers  How to sort the numbers in ascending order   import java.util.*; class SortNumbers{ public static void main(String[] args) { Scanner input=new Scanner(System.in
Java sorting
sorting mechanism. say about Collections.Sort() & Arrays.Sort() that uses
Sorting and Searching
Sorting and Searching  Hi i would really be thankful if someone could help me with this A program is required to ask users to rate the Java programming language using a number in the range 0 - 10. The data input is ended
Sorting an ArrayList
Sorting an ArrayList  print("code sample");Hello All, I am working on a program for school and I am having trouble with sorting my list. The numbers I receive in my output are correct, but not in ascending order as required
Bubble Sorting in Java
Bubble Sorting in Java  Hi, What is Bubble Sorting? Guide me where to learn Bubble Sorting in Java? Thanks   Hi, Check the tutorial at Bubble Sorting in Java. Thanks
ModuleNotFoundError: No module named 'results'
ModuleNotFoundError: No module named 'results'  Hi, My Python... 'results' How to remove the ModuleNotFoundError: No module named 'results... to install padas library. You can install results python with following command
display database results
display database results  how to display database results using php
JDBC: Sorting Table Example
JDBC: Sorting Table Example In this section, you will learn how to sort your...;desc'  as 'ORDER BY column_name desc'ADS_TO_REPLACE_1 Example :  In this example we are arranging student records in descending order
ModuleNotFoundError: No module named 'sorting'
ModuleNotFoundError: No module named 'sorting'  Hi, My Python... 'sorting' How to remove the ModuleNotFoundError: No module named 'sorting... to install padas library. You can install sorting python with following command
Sorting Vector Element using I18N
Sorting Vector Element using I18N       This Example shows you how to sort vector element using I18N. In the code given below we are sorting some elements using some rules
Comparison between the types of sorting in java
Comparison between the types of sorting in java  welcome all i wanna... In terms of timer and put all types in frame and find the timer for example array... of sorting. please help me thanks all
MySQL :Regex Example
MySQL :Regex Example In this tutorial we are going to discuss about regex with example in JDBC. MySQL : Regex You can use Regular Expressions in mysql...:] for whitespace [:punct:] for punctuation [:upper:] for upper case letters MySQL
JDBC ResultSet Example
) database query results. Through this example you can see how to use ResultSet...JDBC ResultSet Example: In this example, we are discuss about ResultSet class... resultset methods. The full code of the example is: package 
jtable displays search results
jtable displays search results   hi sir can u send me full source code for displaying search results into jtable from database n jtable n search button must be within same frame but in different Panel and the size of the frame

Questions by Category