# SQL MIN-MAX Functions

## Definition

In SQL, the `MIN` and `MAX` functions are used to find the minimum and maximum values in a set of data, respectively.

The `MIN` function takes a column name as its argument and returns the smallest value in that column. For example, the following SQL statement finds the smallest `salary` in the `employees` table:

``````SELECT MIN(salary)
FROM employees;
``````

The `MAX` function works in the same way, but returns the largest value in a column instead of the smallest.

## Example

``````SELECT MAX(age)
FROM employees;
``````

You can also use both min and max function together with group by statement:

``````SELECT department, MIN(salary), MAX(salary)
FROM employees
GROUP BY department;
``````

It will give the department wise minimum and maximum salary of the employees.

You can use other aggregate functions like `COUNT()`, `SUM()` or `AVG()` with `MIN()` and `MAX()`.

The `MIN()` and `MAX()` functions can also be used with a `WHERE` clause to filter the data that is used to calculate the minimum and maximum values.

``````SELECT MIN(salary)
FROM employees
WHERE department = 'IT';
``````

This SQL statement finds the smallest `salary` in the `employees` table for employees that are in the "IT" department only.

## Best Practices

There are several best practices to keep in mind when using the `MIN()` and `MAX()` functions in SQL:

• Use indexes: `MIN()` and `MAX()` functions are used to find the smallest or largest value in a column, respectively. Make sure that the column you are searching on has an index, otherwise the query will be slow.

• Use the right data types: The `MIN()` and `MAX()` functions work best with numeric data types (such as `INT`, `BIGINT`, `FLOAT`, etc.). If you are using non-numeric data types (such as `VARCHAR` or `DATE`), you may need to convert the data to a numeric format before using the `MIN()` and `MAX()` functions.

• Use aggregate functions: `MIN()` and `MAX()` are aggregate functions, meaning that they return a single value based on multiple rows of data. Make sure to use them in the context of a `SELECT` statement that also includes a `GROUP BY` clause.

• Use aliases: To make it easier to reference the results of the `MIN()` and `MAX()` functions in the rest of your query, you can use the `AS` keyword to give them an alias.

• Be careful when using `NULL` values: The `MIN()` and `MAX()` functions ignore null values by default. If you have null values in the column you are searching on and you want to include them in your query, you'll need to use the `COALESCE` or `ISNULL` function to replace nulls with a specific value.

• Avoid unnecessary use of the `MAX()` or `MIN()` functions, as using `LIMIT 1` in combination with `ORDER BY` `DESC` or `ASC` on the column in question results in faster queries more often than not.

``````SELECT *
FROM table1
WHERE column_name = (SELECT MAX(column_name)
FROM table1);
``````

you could use:

``````SELECT *
FROM table1
ORDER BY column_name
DESC LIMIT 1;
``````

This can make a big performance improvement in case of large data set.