How to work with Order By Operator in MySql

    The order by function is used to help us order our results, this could be in ascending order, in descending order, or could be based on either numerical value or text value. In such cases, we can use the ORDER BY keyword to achieve our goal. Let's create the table we will use for our examples: Script: 

--first lets drop the table Test (if she exists, if not skip the drop line).
Drop table test;
create table test (id int,name varchar(20),email varchar(20),salary int ,de char(20));
--insert values into table
insert into test values (1,'Eve','[email protected]',1500,'HR');
insert into test values (2,'Jon','[email protected]',2500,'AD');
insert into test values (3,'Mike','[email protected]',3000,'AD');
insert into test values (4,'Paul','[email protected]',3200,'HR');
insert into test values (5,'Mary','[email protected]',1800,'IT');
insert into test values (6,'Jane','[email protected]',2200,'IT');
Check the table to see the data and the structure. 
SQLselect * from test ;
    ID NAME         EMAIL            SALARY  DE
 ---------- -------------------- -------------------- ---------- --  --------
     1  Eve         [email protected]        1500   HR
     2  Jon         [email protected]        2500   AD
     3  Mike       [email protected]        3000   AD
     4  Paul        [email protected]        3200   HR
     5  Mary       [email protected]        1800   IT
     6  Jane        [email protected]       2200   IT
Very good

Ex 1 :

-we will order our result by alphabetical order. -to do so we need to apply our ORDER BY function to a field that holds string values. 
SQLselect id , name from test order by name;
    ID NAME ---------- --------------------
     1 Eve
     6 Jane
     2 Jon
     5 Mary
     3 Mike
       4 Paul
-we see that the id changed their order and the order manner is dictated by Name column, which is in alphabetical order now (from a to z) We can also order them from z to a by using the DESC (descending) function at the end of our query. 
SQLselect id, name from test order by name desc ;
    ID NAME
 ---------- --------------------
     4 Paul
     3 Mike
     5 Mary
     2 Jon
     6 Jane
     1 Eve
We can observe that the order is descending now (from z-a). Note : by default the order by function will sort the values in ascending way, still we have a ASC function we can use.

Example of ASC function

SQLselect id, name from test order by name asc ;
    ID NAME
 ---------- --------------------
     1 Eve
     6 Jane
     2 Jon
     5 Mary
     3 Mike
     4 Paul
-we can see that the result is the same as above when not using the ASC function. Still the ASC is often used . Note : all of the above rules apply for numeric and other values as well.

Copyright 2020. All Rights Reserved