The objective of this post is to explain how to obtain the least and the greatest values across the columns of a table, in Oracle SQL.
Introduction
The objective of this post is to explain how to obtain the least and the greatest values across the columns of a table, in Oracle SQL.
We will start by creating a simple three column table and populate it. Then, we will find the greatest and least values of these columns and return them in a fourth column.
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 create a simple test table for our functions. The statement to create the table is shown bellow. Our table, named TestValues, will only have 3 simple numeric columns, called Val1, Val2 and Val3.
create table TestValues( Val1 Number, Val2 Number, Val3 Number );
You can use the statements bellow to insert some testing data, or use your own values.
insert into TestValues values (1,2,3); insert into TestValues values (1,1,2); insert into TestValues values (7,44,10);
To get the least value of our three columns, we will use the least function. So, we will do a select of our 3 numeric columns and of a fourth column that is the least function applied to the table’s columns.
select val1, val2, val3, least(val1, val2, val3) as least from TestValues;
The result of running this query on SQL Developer is shown bellow, in figure 1. As we can see, the column called “least” has the least value across the original table columns we specified.
Figure 1 – Least value across columns of a table.
To get the greatest value of our three columns, we will use the greatest function. So, we will do another select of our 3 numeric columns and of a fourth column that is the greatest function applied to the table’s columns.
select val1, val2, val3, greatest(val1, val2, val3) as greatest from TestValues;
The result of running this query on SQL Developer is shown bellow, in figure 2. As we can see, the column called “greatest” has the greatest value across the original table columns we specified.
Figure 2 – Greatest value across columns of a table.
We can use these functions with non numeric values. Bellow there are some examples that can be tested for strings. Note that we are doing the select on the DUAL table, to avoid creating another table.
select greatest('house','car','forest' ) from DUAL;
select least('house','car','forest' ) from DUAL;