Teradata MIN (Minimum) Function

Teradata MIN is used to get the minimum value from the value_expression. 

Teradata MIN Syntax

The syntax of the Teradata MIN is as follows.

MIN/MINIMUM (DISTINCT/ALL value_expression)

Here,

• DISTINCT – If DISTINCT is used in the MIN function then all the duplicates and non-null values will be eliminated while calculating the minimum.
• ALL – This is the default value. In this case, all non-null values including duplicates will be counted while calculating the minimum for a group.
• value_expression – a literal or column expression for which the minimum needs to be computed. The expression cannot contain any ordered analytical or aggregate functions.

Teradata MIN Example

Let’s take an example to understand the concept of the Teradata MIN (minimum) function.

Table Preparation

Let’s create a volatile table name products and insert some dummy data into it.

CREATE VOLATILE TABLE Products
(
ProductID INT NOT NULL,
ProductName varchar(100) NOT NULL,
SupplierID INT NOT NULL,
CategoryID INT NOT NULL,
Unit INT NOT NULL,
Price INT
)
unique primary index(ProductID)
on commit preserve rows;

insert into products (1,'Batteries',1,1,10,25);
insert into products (2,'Wooden single drawer',1,2,20,47);
insert into products (3,'Classic wooden chair',2,2,15,31);
insert into products (4,'Magazine chair',1,1,10,NULL);
insert into products (5,'Chair with love',2,1,20,31);
insert into products (6,'Round chair',2,2,15,11);

Now check the data of the table using the select statement.

select * from Products order by 1;

Output

1) Teradata MIN: Finding minimum value from a column

You can use the following query to find the minimum value from a column. In this example, we are calculating the minimum value from the column Price.

select min(Price) from Products;

This can be also written as,

select minimum(Price) from Products;

Output

2) Teradata MIN: using ALL

select min(ALL Price) from Products;

Output

3) Teradata MIN: using DISTINCT

select min(DISTINCT Price) from Products;

Output

4) Teradata MIN: using group by clause

select CategoryID,min(Price) from Products
group by CategoryID
order by CategoryID;

Output