4 Things to Know About the GREATEST Function in Oracle

The GREATEST function in Oracle is one that is helpful, but I haven't seen used very often. If you do use it, keep these things in mind.
What Is The GREATEST Function?
The first thing to know about the function is what it does.
GREATEST finds the greatest value in a list of provided values.
The syntax is:
GREATEST ( expr1, [ expr_n] )
You provide it with a set of expressions, and the function will find the greatest of these.
It's Not an Aggregate Function
Unlike the MAX function, the GREATEST function is not an aggregate function. It doesn't accept multiple rows and transform them into a single row.
The GREATEST function will return a row for each row that you provide. So, if you have a query like this:
SELECT first_name, GREATEST(score_jan, score_feb, score_mar)
FROM students;
This query will return every first_name, and the highest value from those three columns. It doesn't aggregate the values - they will be evaluated for each row and will most likely be different.
Works with Numbers, Characters, and Dates
The GREATEST function works with the three main data types - numbers, characters, and dates.
If the provided values are numeric, Oracle works out the highest number.
If the provided values are characters, Oracle returns the last value if they were sorted alphabetically.
If the provided values are dates, Oracle returns the latest date.
This makes it quite a flexible function. You don't need to convert data types for the function to work. Simply provide the columns or values you need to the function.
If Any Expressions Are NULL, NULL Will Be Returned
As I mentioned earlier, the Oracle GREATEST function works with several different data types.
It can also accept NULL values. However, if any of the provided values are NULL, then the entire function returns NULL.
For example, let's take a look at this query:
SELECT GREATEST(10, 15, 8, 4, 71, 45)
FROM dual;
This query will return 71 because that's the highest number.
Let's see an example using columns. Assuming we have one row in a "scores" table with these values:
score_jan = 81
score_feb = 72
score_mar = 85
If we run a GREATEST on these columns, we get the result of 85.
SELECT GREATEST(score_jan, score_feb, score_mar)
FROM scores;
Now, what if we update the table to say that score_jan is NULL?
score_jan = NULL
score_feb = 72
score_mar = 85
SELECT GREATEST(score_jan, score_feb, score_mar)
FROM scores;
The same query will return NULL, because one of the input values is NULL.
So, those are some things that you'll need to remember when using the GREATEST function. It's a useful function, so keep these points in mind!

Views: 20


You need to be a member of Oracle Community to add comments!

Join Oracle Community

Oracle Community On

I'm not a fan of advertising, and so I will not be including any advertisements on OracleCommunity.net. However, managing this community does not come without cost! If you are willing to donate to help pay for the monthly community fees and domain services I accept Bitcoin and PayPal donations.

Donate Bitcoins



Blog Posts

Do What You Do Best and Outsource the Rest

One of the most valuable assets of an organization, data lies at the heart of business. It impacts everything from customer service to email deliverability and ultimately revenue generation. If the data that you have is not accurate, it will have a direct impact on your company’s ability to meet…


Posted by Alvaro Dee on February 7, 2017 at 2:07am

Draw Actionable Insights and Make Better Decisions with Data Mining Services

Data Mining is the process of analyzing and extracting crucial information from a database and presenting the same in an appropriate way to help business cut cost and boost revenue. In simple words it is the process of drawing crucial and actionable insights, which is imperative for better decision making.

Data mining is crucial for effective management of your business. It is also used for verifying whether or not the strategies are going as planned so as to provide desired results.…


Posted by Alvaro Dee on February 1, 2017 at 4:02am

Attain Data Accuracy, Competitive Pricing, and Quick Turnaround Time by Outsourcing Data Entry Services

In order to focus on crucial business activities, organizations worldwide prefer outsourcing data entry services to reliable service providers. Though redundant but highly crucial, data entry still remains an important component of document management and requires vigorous quality checks. Outsourcing data entry tasks to experts ensures affordable and quality output while saving time and resources that can be…


Posted by Alvaro Dee on January 31, 2017 at 4:49am

Present and Exchange Documents Reliably With Portable Document Format

Are you having data in large volumes that needs to be converted from hard copy to PDF format? Can you imagine how much time it would take you to manually convert this data? Well, if you know, then what’s the point of putting in lot of efforts and time?

With the help of PDF data entry services, this task can be made a lot easier. Data entry experts can help you save time and efforts…


Posted by Alvaro Dee on January 25, 2017 at 3:38am

© 2017   Created by Steve Karam.   Powered by

Badges  |  Report an Issue  |  Terms of Service