Oracle Max () function and min () function

You can use the max () function when you need to know the maximum value in a column, and you can use the min () function when you need to know the minimum value in a column. The syntax is as follows.

SELECT MAX (column_name)/MIN (column_name)

From table_name

Description: The data in column column_name can be numeric, string, or datetime data type. The MAX ()/min () function returns a single value of the same data type as the column being passed.

Example 7 use of the MAX () function

Query the maximum age of the teacher in the teacher table. Instance code:

SELECT MAX (age) as MAXAGE

From TEACHER

The results of the operation are shown in 1.

Figure 1TEACHER Max age of teacher in table

However, it is not particularly useful to get this result in practical applications, as the information that is often wanted is the teacher's number, name, gender, and so on, with the largest age.

However, SQL does not support the following SELECT statements.

SELECT Tname, Dname, Tsex, MAX (age)

From TEACHER

Because the aggregation function handles data sets, in this case the Max function sees the entire teacher table as a group, and the data for Tname, Dname, and tsex do not have any groupings, so the SELECT statement has no logical meaning. In the same vein, the following code is invalid.

SELECT Tname, Dname, tsex,sal, age

From TEACHER

WHERE Age=max (age)

The solution to this problem is to use a subquery in the WHERE clause to return the maximum value, and then, based on the maximum value returned, to query the relevant information.

Instance 8 returns the maximum value in the WHERE clause using a subquery

Inquire about the teacher number, name, gender, etc. of the oldest teachers in the teacher table.

Instance code:

SELECT Tname, Dname, Tsex, SAL, age

From TEACHER

WHERE age= (SELECT MAX (age) from TEACHER)

The results of the operation are shown in 2.

Figure 2 Using a subquery to return the maximum value in the WHERE clause

The MAX () and Min () functions can be used not only for numeric data, but also for data of a string or datetime data type.

Instance max () function for character data

As in the following code:

SELECT MAX (tname) as Maxname

From TEACHER

The results of the operation are shown in 3.

Figure 3 Results of using Max in a string data type

It is visible that the maximum value can also be obtained for a string.

Description
The maximum value of the character data is arranged in the order of the first letter by the A~z, and the farther back, the greater the value. Of course, for Chinese characters are arranged according to their full spelling, if the first character is the same, then compare the next character, and so on.

Of course, the data of the date and time type can also be the maximum/minimum value, its size is the date time, sooner or later, the earlier the value of the smaller, such as the following instance.

Instance MAX (), MIN () function for time-based data

Check the time of the earliest and latest exam courses from the course table. The structure and data of the course table can be found in table 6-1 of Section 6.1 of this book. Instance code:

SELECT MIN (CTest) as Early_date,

MAX (CTest) as Late_date

From COURSE

The results of the operation are shown in 4.

Figure 4COURSE Test time for the earliest and latest exam courses in the table

It is visible that the data type of the returned result is the same as the data type defined by the column.

Attention
The Max () (min ()) function ignores null values when determining the maximum (minimum) value in a column. However, if the value of all rows in the column is null, the Max ()/min () function returns a null value.