Cloud

GREATEST

The GREATEST function returns the greatest value from a set of values. The arguments must be of compatible types. For example, comparing a text value with a number returns an error.

Syntax

The syntax for this function is:

GREATEST(value_1, [value_n])

Where:

  • value_1: Represents the first value.

  • value_n: Represents one or more additional values, separated by commas.

  • NULL values within the expressions are ignored.

  • The result is NULL if all expressions evaluate to NULL.

Examples

Basic usage

SELECT GREATEST(3,5,8,9,10);

The query returns 10, the greatest value among the provided values.

greatest
---------
     10

String comparison

String comparison is also supported:

SELECT GREATEST('apple', 'banana', 'cherry');

The query returns 'cherry', the greatest string in lexicographic order.

greatest
----------
 cherry

Handle NULL values

NULL values are ignored when determining the greatest value:

SELECT GREATEST (5,null,9);

The query returns 9, the greatest non-NULL value.

greatest
----------
        9

Positive and negative numbers

Negative numbers can also be compared:

SELECT GREATEST (4,-4,-8,8);

The query returns 8, the greatest value among the provided numbers.

greatest
----------
        8

Use table data

You can also use GREATEST to find the greatest value across columns. For example, create a table named Student that stores student names and scores.

CREATE TABLE Student(
    Student_name TEXT,
    Student_Class TEXT,
    Subject1 INT,
    Subject2 INT,
    Subject3 INT,
    Subject4 INT
);

INSERT INTO
    Student(Student_name, Student_Class, Subject1, Subject2, Subject3, Subject4)
VALUES
    ('Sayan', 'Junior', 81, 90, 86, 92 ),
    ('Nitin', 'Junior', 90, 84, 88, 91 ),
    ('Aniket', 'Senior', 81, 80, 87, 95 ),
    ('Abdur', 'Junior', 85, 90, 80, 90  ),
    ('Sanjoy', 'Senior', 88, 82, 84, 90 );

Use the SELECT statement to view all the records:

SELECT * FROM Student;
student_name | student_class | subject1 | subject2 | subject3 | subject4
--------------+---------------+----------+----------+----------+----------
 Sayan        | Junior        |       81 |       90 |       86 |       92
 Nitin        | Junior        |       90 |       84 |       88 |       91
 Aniket       | Senior        |       81 |       80 |       87 |       95
 Abdur        | Junior        |       85 |       90 |       80 |       90
 Sanjoy       | Senior        |       88 |       82 |       84 |       90

Find the greatest mark for each student across all subjects:

Select Student_name, GREATEST(Subject1, Subject2, Subject3, Subject4) AS Greatest_Mark
FROM Student;
student_name | greatest_mark
--------------+---------------
 Sayan        |            92
 Nitin        |            91
 Aniket       |            95
 Abdur        |            90
 Sanjoy       |            90