The objective of this post is to explain how to perform the ordering of a table in Oracle SQL, using a list of values.
Introduction
The objective of this post is to explain how to perform the ordering of a table in Oracle SQL, using a list of values.
We will start by creating a simple two column table and populate it. Then, we will order the table by one if its columns, by a defined list of values.
I will be running the queries in Oracle SQL Developer, but it should also work fine for other clients.
The SQL queries
First, we will be creating a simple table with two columns. This will be a dummy example representing a table with names of people and their jobs.
The table will be called “Worker” and we will have two columns: “WorkerName“, for the name of the person, and “WorkerJob“, for the job of the person.
Check the statement bellow and run it for the table to be created. Note that this is a simple example, so we are not going to use any table constraints.
create table Worker(
WorkerName varchar(255),
WorkerJob varchar(255)
);
You should receive a “Table created” message. Now, we will insert some values in our table. The statements needed for our example are shown bellow. You can copy them and execute them against your database. You should get a “Row inserted” message for each one of them.
insert into Worker values ('John','Fisherman');
insert into Worker values ('Terry','Policeman');
insert into Worker values ('Anna','Fisherman');
insert into Worker values ('Louisa','Doctor');
insert into Worker values ('Bob','Doctor');
insert into Worker values ('Jack','Doctor');
insert into Worker values ('Daniel','Nurse');
If you want this data to be persisted after ending your session, you can send a commit statement.
Now, we will send a query to get both columns of each record of the database, ordered by the WorkerJob column. Our order will be a custom one, according to the following list:
1º – Fisherman
2º – Doctor
3º – Policeman
4º – Nurse
As we can see, our custom order is not alphabetical, so we will need to translate it to something we can order. Oracle SQL allows us to do this very easily and in a compact notation by using the decode function.
The function first argument is the column to which we want to apply the decoding, followed by pairs of original value – decoded value [1].
To do so, we will do a query with a select from table Worker, ordered by the list of values indicated before. So, we will decode the jobs descriptives to numbers, with the order from our list of values. The query is shown bellow.
select WorkerName, WorkerJob
from Worker
order by decode(WorkerJob,
'Fisherman',1,
'Doctor',2,
'Policeman',3,
'Nurse', 4) asc;
Note that the table will be ordered by the decoded values and not the original us, thus giving the desired effect.
For our testing data, we should get a result similar to figure 1 upon running the query. Note the result in the bottom, which corresponds to all the records inserted ordered by the list of values given in the decode function.
Figure 1 – Running the order by query with decode function on SQL Developer.
Just as an additional example, the query indicated bellow will order the results by our list of values and then by the WorkerName column. In this second ordering, since we don’t specify any type of encoding, we will get an alphabetical order.
select WorkerName, WorkerJob
from Worker
order by decode(WorkerJob,
'Fisherman',1,
'Doctor',2,
'Policeman',3,
'Nurse', 4) asc,
WorkerName asc;
The result is shown in figure 2.
Figure 2 – Ordering the table columns by an additional criteria.
If you don’t wan’t to persist this table on your database, you can drop it, as indicated bellow.
drop table worker;
References
[1] http://www.dba-oracle.com/t_decode_function.htm
Technical details
Oracle Database 11g Express Edition Release 11.2.0.2.0 – 64bit