Oracle SQL: Least and greatest values across columns

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.

Oracle SQL least value across columns.png

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.

oracle-sql-greatest-value-across-columns

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;
Advertisements
This entry was posted in SQL and tagged , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s