Updated June 20, 2023
Introduction to DB2 order by
DB 2 provides a different clause to the user; the order by clause is one of the clauses that is provided by DB 2. By using the order by clause, we can arrange the data as per the user requirement in either ascending order or descending order. When we execute the select statement, that means we try to fetch the records from the specified table, the order of records in output is not specified. So we need to sort the result, or we can say that we need to sort the values of the specified column. At that time, we can use order by clause. Basically, order by clause is an optional clause of the select statement. In this topic, we are going to learn about DB2 order by.
Syntax
select colm 1, colm 2,……colm N from specified table order by specified expression [ASC | DESC], specified expression [ASC | DESC], …………..;
Explanation
In the above syntax, we use a select clause with different parameters as follows.
colm 1, 2, N: it is column names that are created inside the specified table.
Specified table: Specified table means the user creates actual table names.
After that, we need to specify the expression1, expression 2, .N, which means it is the column name that we want to sort.
The second thing we ASC to sort the column in ascending order that means low to high, and DESC is used to sort the column in descending order that means high to low.
Basically, ASC or DESC is an optional part of this syntax; if we skip this, then DB 2 uses ASC by default. Evaluating the select statement, then it first executes from then select and order by clause.
How order by works in DB2?
Now let’s see how order by clause in DB 2 is as follows.
On the off chance that a solitary sort determination (one sort-key with related course) is distinguished, the rows are ordered by the estimations of that sort in particular. In the event that more than one sort detail is recognized, the rows are sorted by the estimations of the previously distinguished sort particular, at that point by the estimations of the second distinguished sort determination, etc. Each sort-key can’t have an information kind of CLOB, DBCLOB, BLOB, XML, unmistakable sort on any of these sorts, or organized sort.
A column name in the select clause can be distinguished by a sort-key that is a basic number or a straightforward column name. An anonymous section in the select rundown should be recognized by a straightforward number or, now and again, by a sort-key-articulation that coordinates with the articulation in the select rundown (see subtleties of sort-key-articulation). A section is anonymous if the AS statement isn’t indicated and it is obtained from a steady, an articulation with administrators, or a capacity.
Order is acted as per examination rules. In the event that an ORDER BY statement contains decimal gliding point sections and numerous portrayals of similar numbers exist in these segments, the request of the different portrayals of a similar number is vague. The invalid worth is higher than any remaining qualities. In the event that the ORDER BY condition doesn’t totally arrange the lines, lines with copy estimations of all recognized sections are shown in a discretionary request.
Column Name
Typically distinguishes a column of the outcome table. For this situation, a straightforward column name should be the column name of a named section in the select rundown.
The basic column name can likewise distinguish a segment name of a table, see, or settled table recognized in the FROM clause if the question is a subselect. This incorporates columns characterized as verifiably covered up. A mistake happens in the accompanying circumstances: In the event that the subselect indicates DISTINCT in the select-clause. On the off chance that the subselect produces an assembled result and the basic section name isn’t a gathering articulation. Figuring out which column is utilized for requesting the outcome is depicted under Column names in sort keys in the Notes section.
Basic number
It should be more prominent than 0 and not more noteworthy than the number of columns in the outcome table. The integer number n distinguishes the nth section of the outcome table.
Sort-key-expression
An expression that is only a column name or an unsigned number consistent. The question to which requesting is applied should be a sub-select to utilize this type of sort-key. The sort-key-expression cannot exclude a connected scalar full select or a function with an outer activity. Any column name inside a sort-key-expression should adjust to the guidelines depicted under Column names in sort keys in the Notes area.
Examples
Now let’s see the different examples of order as follows.
First, we need to create a new table by using the following statement as follows.
create table company_1 (Comp_Id int(20), comp_name varchar(30),
comp_address varchar(30));
Explanation
In the above example, we use a create table statement to create a new table name as a company with different attributes such as Comp_id, comp_name, and comp_address with different data types and sizes, as shown in the above statement.
For confirmation, insert some records by using the following insert into the statement as follows.
insert into company_1 (Comp_Id, comp_name,comp_address) values(1, "HP", "Mumbai"), (2, "Dell", "Pune"), (3, "LG", "Delhi"), (4, "Bajaj", "Kolkatta"), (5, "Usha", "Kochi");
select * from company;
Explanation
In the above example, we use to insert into a statement to insert new records into the company_1 table. The end out we illustrate by using the following screenshot as follows.
Now perform order by clause as follows.
Sort the single column by using DB2 order by clause as follows.
select Comp_Id, comp_name from company_1 order by comp_name;
Explanation
In the above example, we use a select clause with the order by clause; notice here we don’t specify any ASC or DESC for comp_name column name that means it uses ASC by default. The end out we illustrate by using the following screenshot as follows.
Now sort two columns by DB 2 order as follows.
select Comp_Id, comp_name, comp_address from company_1 order by comp_name DESC, comp_address;
Explanation
In the above example, we try to sort two columns at a time; here, we need to arrange comp_name in DESC order, and comp_address takes by default order. The end out we illustrate by using the following screenshot as follows.
In this way, we can use order by clause with length () function as per the requirement.
Conclusion
We hope from this article you have understood about the DB 2 Order by. From the above article, we have learned the basic syntax of Order by, and we also see different examples of Order by. From this article, we learned how and when we use the DB 2 Order by.
Recommended Articles
This is a guide to DB2 order by. Here we discuss the basic syntax of Order by, and we also see different examples of Order by. You may also have a look at the following articles to learn more –
