Oracle Max () function and min () function

Source: Internet
Author: User
Tags dname

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:



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)


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


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


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


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.

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


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.

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.