Set up docs MCPDescription
The If combinator can be applied to the argMax
function to find the value of arg that corresponds to the maximum value of val for rows where the condition is true,
using the argMaxIf aggregate combinator function.
The argMaxIf function is useful when you need to find the value associated with
the maximum value in a dataset, but only for rows that satisfy a specific
condition.
Example usage
In this example, we’ll use a sample dataset of product sales to demonstrate how
argMaxIf works. We’ll find the product name that has the highest price, but
only for products that have been sold at least 10 times.
CREATE TABLE product_sales
(
product_name String,
price Decimal32(2),
sales_count UInt32
) ENGINE = Memory;
INSERT INTO product_sales VALUES
('Laptop', 999.99, 10),
('Phone', 499.99, 15),
('Tablet', 299.99, 0),
('Watch', 1199.99, 5),
('Headphones', 79.99, 20);
SELECT argMaxIf(product_name, price, sales_count >= 10) AS most_expensive_popular_product
FROM product_sales;The argMaxIf function will return the product name that has the highest price
among all products that have been sold at least 10 times (sales_count >= 10).
In this case, it will return ‘Laptop’ since it has the highest price (999.99)
among the popular products.
┌─most_expensi⋯lar_product─┐
1. │ Laptop │
└──────────────────────────┘