The ORDER BY statement is utilized to organize the substance of the outcome set in the request it utilizes the catchphrases, ASC speaking to rising request and DESC speaking to slipping request. In the event that you don't make reference to both of these, the substance will be orchestrated in rising request naturally.
Syntax
Following is the grammar of the ORDER BY statement −
SELECT * FROM table_name ORDER BY column_name ASC|DESC.
Example
Assume we have a table named Employees in the data set with the accompanying records −
ID |NAME |SALARY |LOCATION
------------------------------------------------------------------------------
1 |Amit |30000 |Vijayawada
2 |Kalyan |40000 |Vishakhapatnam
3 |Renuka |50000 |Delhi
4 |Archana |15000 |Vijayawada
5 |Trupthi |45000 |Kochin
6 |Suchatra |33000 |Vijayawada
7 |Rahul |39000 |Lucknow
The accompanying inquiry organizes the substance of the table in rising request dependent on the name of the Employee.
ij> SELECT * FROM Employees ORDER BY Name;
This will create the accompanying yield −
ID |NAME |SALARY |LOCATION
---------------------------------------------------------------
1 |Amit |30000 |Hyderabad
4 |Archana |15000 |Mumbai
2 |Kalyan |40000 |Vishakhapatnam
7 |Rahul |39000 |Lucknow
3 |Renuka |50000 |Delhi
6 |Suchatra |33000 |Pune
5 |Trupthi |45000 |Kochin
7 rows selected
Additionally, following inquiry masterminds the substance of the table in slipping request dependent on the compensation of the Employee −
ij> SELECT * FROM Employees ORDER BY Salary DESC;
This will create the accompanying yield −
ID |NAME |SALARY |LOCATION
---------------------------------------------------------------
3 |Renuka |50000 |Delhi
5 |Trupthi |45000 |Kochin
2 |Kalyan |40000 |Vishakhapatnam
7 |Rahul |39000 |Lucknow
6 |Suchatra |33000 |Pune
1 |Amit |30000 |Hyderabad
4 |Archana |15000 |Mumbai
7 rows selected
Sorting Data using JDBC program
This part shows you how to sort the substance of a table in Derby utilizing JDBC. You can mastermind records all together utilizing the ORDER BY provision and the catchphrases ASC (indicating climbing request), and DSC (signifying dropping request).
In the event that you need to demand the Derby network worker utilizing network customer, ensure that the worker is going. The class name for the Network customer driver is org.apache.derby.jdbc.ClientDriver and the URL is jdbc:derby://localhost:1527/DATABASE_NAME;create=true;user=USER_NAME;passw ord=PASSWORD".
Follow the means offered beneath to sort the records of a table in Apache Derby −
Step 1: Register the driver
To speak with the information base, most importantly, you need to enroll the driver. The forName() technique for the Class acknowledges a String esteem speaking to a class name loads it in to the memory, which consequently enrolls it. Register the driver utilizing this strategy.
Step 2: Get the connection
By and large, the initial step we do to convey to the data set is to associate with it. The Connection class speaks to the actual association with a data set worker. You can make an association object by summoning the getConnection() technique for the DriverManager class. Make an association utilizing this strategy.
Step 3: Create a statement object
You need to make a Statement or PreparedStatement or, CallableStatement objects to send SQL articulations to the information base. You can make these utilizing the techniques createStatement(), prepareStatement() and, prepareCall() individually. Make both of these articles utilizing the proper technique.
Step 4: Execute the query
In the wake of making an assertion, you need to execute it. The Statement class gives different techniques to execute a question like the execute() strategy to execute an explanation that profits more than one outcome set. The executeUpdate() strategy executes questions like INSERT, UPDATE, DELETE. The executeQuery() strategy brings information back. Use both of these techniques and execute the assertion made beforehand.
Example
Following JDBC model shows how to sort the records of a table in Apache Derby utilizing JDBC program. Here, we are interfacing with a data set named sampleDB (will make in the event that it doesn't exist) utilizing the implanted driver.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SortData {
public static void main(String args[]) throws SQLException, ClassNotFoundException {
//Registering the driver
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
//Getting the Connection object
String URL = "jdbc:derby:SampleDB;create=true";
Connection conn = DriverManager.getConnection(URL);
//Creating the Statement object
Statement stmt = conn.createStatement();
//Creating a table and populating it
String query = "CREATE TABLE Employees("
+ "Id INT NOT NULL GENERATED ALWAYS AS IDENTITY, "
+ "Name VARCHAR(255), Salary INT NOT NULL, "
+ "Location VARCHAR(255), "
+ "PRIMARY KEY (Id))";
String query = "INSERT INTO Employees("
+ "Name, Salary, Location) VALUES "
+ "('Amit', 30000, 'Hyderabad'), "
+ "('Kalyan', 40000, 'Vishakhapatnam'), "
+ "('Renuka', 50000, 'Delhi'), "
+ "('Archana', 15000, 'Mumbai'), "
+ "('Trupthi', 45000, 'Kochin'), "
+ "('Suchatra', 33000, 'Pune'), "
+ "('Rahul', 39000, 'Lucknow'), "
+ "('Trupti', 45000, 'Kochin')";
//Executing the query
String query = "SELECT Location, SUM(Salary) " + "from Employees GROUP BY Location";
ResultSet rs = stmt.executeQuery(query);
while(rs.next()) {
System.out.println("Salary: "+rs.getString(1));
System.out.println("Location: "+rs.getString(2));
System.out.println(" ");
}
}
}
Output
On executing the above program, you will get the accompanying yield −
Salary: Chennai
Location: 43000
Salary: Delhi
Location: 50000
Salary: Hyderabad
Location: 30000
Salary: Kochin
Location: 45000
Salary: Lucknow
Location: 39000
Salary: Mumbai
Location: 15000
Salary: Pune
Location: 33000