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

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

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

Ads