Class Greatest

Namespace

YndigoBlue.Velocity.Functions

Assembly

YndigoBlue.Velocity.dll

Represents the GREATEST function, which returns the largest of two or more values.

public class Greatest : Function, ICheckItem, IDefaultItem, IFilterItem, IElement

Inheritance

object

Function

Greatest

Implements

ICheckItem

IDefaultItem

IFilterItem

IElement

Remarks

GREATEST compares values across a row, unlike the Max aggregate which finds the largest value down a column across many rows. GREATEST(price, floor_price) yields one value per row; MAX(price) yields one value for the whole group.

It pairs naturally with Case to classify a row by whichever of several columns is the largest — the simple case form compares its operand against each branch value, and ties are broken by branch order.

NULL handling differs by datasource and Velocity does not normalize it.

NULL arguments ignored	PostgreSQL, SQL Server
Any NULL makes the whole result NULL	Oracle, MySQL, DB2, SQLite, Teradata

Velocity deliberately does not inject a NULL guard of its own: doing so would silently change the meaning of a query that genuinely wants a NULL to propagate, and would make this function mean something different from the GREATEST every one of these databases documents. Where a missing value has a defined meaning, say so explicitly with Coalesce — that yields identical results on every datasource:

var counts = raceColumns
    .Select(c => (IElement)new Coalesce([ c, new Literal<int>(0) ]))
    .ToList();
var greatest = new Greatest(counts);

Rendered as GREATEST on every supported datasource except SQLite, which spells the same operation as its multi-argument scalar MAX. Note that SQL Server gained GREATEST in SQL Server 2022 (16.x); earlier versions do not support it.

Examples

Clamping a value to a floor:

var schema = manager.LoadSchema("sales");
var products = schema["products"];

var query = new Query()
    .Select([ products["name"], new Expression("effective_price", new Greatest([ products["price"], products["floor_price"] ])) ])
    .From(products);

var results = manager.Retrieve(query);

// SQL: SELECT name, GREATEST(price, floor_price) AS effective_price FROM products

Classifying a row by whichever of several counts is the plurality, ties broken by column order:

Column[] counts = [ names["white_count"], names["black_count"], names["hispanic_count"] ];
string[] labels = [ "White", "Black", "Hispanic" ];

// Coalesce so a missing count reads as zero rather than voiding the whole comparison.
var guarded = counts.Select(c => (IElement)new Coalesce([ c, new Literal<int>(0) ])).ToList();

var raceCase = new Case(new Greatest(guarded));

for (int i = 0; i < counts.Length - 1; i++)
{
    raceCase.When(guarded[i], new Literal<string>(labels[i]));
}

raceCase.Else(new Literal<string>(labels[^1]));

var update = new Update(names);
update.SetFieldAsCase("race", raceCase);

manager.UpdateRecords(update);

Constructors

Greatest(IEnumerable<IElement>)

Initializes a new instance of GREATEST with a collection of elements.