# How to use SQL `MIN` and `MAX` In SQL, `MIN` and `MAX` are aggregate functions that are used to find the **smallest and largest values** in tables and columns. They optimize and accelerate data queries and facilitate efficient data evaluation for determining trends, anomalies and processes. ## What are SQL `MIN` and `MAX`? SQL `MIN` and `MAX` are aggregate functions that perform calculations with numerical values and return a single value. You can use `MIN` and `MAX` in datasets, tables, columns or rows to **find and compare the smallest and largest values**. `MIN` and `MAX` significantly **speed up complex data evaluations**, making them essential functions in SQL. They allow you to find anomalies or to quickly filter product, price and cost tables for minimum and maximum values. Depending on which optional parameters you use, you can also combine the functions with [SQL commands](https://www.ionos.ca/digitalguide/server/configuration/sql-commands/) like [WHERE](https://www.ionos.ca/digitalguide/server/configuration/sql-where/), [JOIN](https://www.ionos.ca/digitalguide/server/configuration/sql-join/), `HAVING` and `GROUP BY`. Tip You want to learn about [SQL](https://www.ionos.ca/digitalguide/server/know-how/what-is-sql/) but are still a beginner? Check out our [SQL introduction with examples](https://www.ionos.ca/digitalguide/server/configuration/sql-introduction-with-examples/)! ## What is the syntax of SQL `MIN` and `MAX`? SQL `MIN` and `MAX can be used alone for **simple data queries** or in combination with other [SQL operators](https://www.ionos.ca/digitalguide/server/configuration/sql-operators/) **for more complex analyses**. The syntax looks like this: ```sql MIN(column or string) MAX(column or string) ``` If you want to apply the function to a subset of numerical values in your dataset, combine `MIN` and `MAX` with the **SQL command `SELECT` and a `FROM` clause**. You can use them to select a table and a column or row like this: ```sql SELECT MIN(column or string) FROM table MAX(column or string) FROM tablename ``` You can also use these additional parameters and commands to make your query more precise: - `DISTINCT`: Eliminates duplicates and duplicate rows in data queries to get unique lists - `WHERE`: Excludes certain records before applying `MIN` and `MAX` and concentrates only on the subsets you define - `GROUP BY`: Groups queried records into one or more columns based on, e.g., region or category, making it possible to analyze trends and patterns - `HAVING`: Filters results after the aggregation of min and max values, displaying results that meet certain criteria - `BETWEEN`: In combination with `MIN` and `MAX`, limits results to a certain range of values - `JOIN`: Allows you to combine min and max values from multiple tables into a single table ## What are SQL `MIN` and `MAX` used for? There are countless uses for SQL `MIN` and `MAX`, especially when they’re combined with other parameters. Some possible use cases included: - Filtering highest and lowest prices in product tables - Returning largest and smallest files - Salary analyses for employees - Showing the frequency of strings, words or values in a dataset - Detecting anomalies that exceed thresholds or average values - Recognizing performance peaks and weaknesses - Analyzing inventories and products - Narrowing down data analyses for large datasets Tip Powerful data management calls for a suitable hosting solution for database management systems. [SQL Server Hosting](https://www.ionos.ca/cloud/sql-server-hosting "SQL Server Hosting from IONOS") from IONOS provides plans tailored to your needs. Take advantage of fast access times, high performance, reliability and data security. ## Examples of SQL `MIN` and `MAX` We’ll now turn to some practical examples that show how SQL `MIN` and `MAX` are applied. In our examples, we’ll look at a **table with products and their prices**. Note that we’ll use AS in the syntax to name the column for `MIN` and `MAX` in the target table. Our example table:
Product Price Category
Shampoo $22.90 Hair
Hand lotion $45.99 Hands
Foot lotion $11.99 Feet
Body lotion $9.99 Body
Now we’ll get the **products with the highest and lowest price** from the table and return them as single lines: `MIN`: ```sql SELECT MIN(price) FROM products; ``` `MAX`: ```sql SELECT MAX(price) FROM products; ``` If you want to return the two values in a table, use the following syntax and define the column with the min and max values using “AS”. ```sql SELECT MIN(price) AS min_price, MAX(price) AS max_price FROM products ``` You can make your search even more precise **using additional commands and parameters**. If you want to display the minimum and maximum values from each product category, you can use the `GROUP BY` command: ```sql SELECT category, MIN(price) AS min_price, MAX(price) AS max_price FROM products GROUP BY category ``` In the target table, you’ll get the lowest and highest values **grouped according to the category of the product**. `HAVING` allows you to additionally filter values that **fulfill certain criteria**. You can specify, for example, that the minimum value of a product shouldn’t be included if it falls below a certain threshold. The syntax for that would look as follows: ```sql SELECT category, MAX(price) AS max_price FROM products GROUP BY category HAVING MIN(price)>10; ``` When this code is used on our example table above, the body lotion that costs $9.99 would not be included in the target table. ## What are some alternatives to SQL `MIN` and `MAX`? SQL `MIN` and `MAX` is one of **SQL’s aggregate functions**. Aside from those two functions, there are number of other similar functions that can be used for returning aggregate values: - `AVG`: [SQL AVG](https://www.ionos.ca/digitalguide/server/configuration/sql-avg/) returns the average value of a table or column – for example, the average temperature of months in a year - `COUNT`: [SQL COUNT](https://www.ionos.ca/digitalguide/server/configuration/sql-count/) counts the number of records in a table or column - `SUM`: [SQL SUM](https://www.ionos.ca/digitalguide/server/configuration/sql-sum/) returns the sum of all the values in a column - `BETWEEN`: [SQL BETWEEN](https://www.ionos.ca/digitalguide/server/configuration/sql-between/) analyzes the data in a value range - `LIKE`: The operator [SQL LIKE](https://www.ionos.ca/digitalguide/server/configuration/sql-like/) returns numerical values or strings that match a search pattern This is a markdown version of: [https://www.ionos.ca/digitalguide/server/configuration/sql-min-and-max/](https://www.ionos.ca/digitalguide/server/configuration/sql-min-and-max/) for AI/LLM consumption.