PostgreSQL - MIN Function

PostgreSQL - MIN() Function

The PostgreSQL MIN() function is an aggregate function that returns the minimum value from a set of values in a specified column. It is essential for data analysis and reporting.

Basic Usage

To understand the MIN() function, consider the following employees table:

-- Sample employees table
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC(10, 2)
);

INSERT INTO employees VALUES
(1, 'Paul', 'IT', 60000),
(2, 'Allen', 'Engineering', 75000),
(3, 'Teddy', 'IT', 62000),
(4, 'Mark', 'Engineering', 95000),
(5, 'David', 'Sales', 85000),
(6, 'Kim', 'Sales', 82000),
(7, 'James', 'IT', 58000);

To find the lowest salary across all employees, you would use the following command:

SELECT MIN(salary) AS lowest_salary FROM employees;

The statement above will produce the following result:

 lowest_salary
---------------
      58000.00
(1 row)

Using MIN() with GROUP BY

A more powerful use of MIN() is to find the minimum value for different groups of rows. For example, to find the lowest salary within each department, you can use MIN() with the GROUP BY clause.

SELECT
    department,
    MIN(salary) AS lowest_salary_in_dept
FROM employees
GROUP BY department
ORDER BY department;

This query groups employees by their department and then finds the minimum salary for each group, producing the following result:

  department  | lowest_salary_in_dept
-------------+-------------------------
 Engineering |                75000.00
 IT          |                58000.00
 Sales       |                82000.00
(3 rows)

Combining MIN() with Other Aggregates

You can use MIN() alongside other aggregate functions like MAX() and AVG() to get a comprehensive summary of your data in a single query.

SELECT
    department,
    MIN(salary) AS min_sal,
    MAX(salary) AS max_sal,
    AVG(salary)::numeric(10,2) AS avg_sal
FROM employees
GROUP BY department;

This query provides a detailed salary breakdown per department:

  department  | min_sal  | max_sal  |  avg_sal
-------------+----------+----------+-----------
 Engineering | 75000.00 | 95000.00 | 85000.00
 IT          | 58000.00 | 62000.00 | 60000.00
 Sales       | 82000.00 | 85000.00 | 83500.00
(3 rows)

Important Considerations

• NULL Values: The MIN() function ignores NULL values. If all values in a group are NULL, it will return NULL.
• Data Types: MIN() can be used on numeric, string, and date/time data types. For strings, it returns the first value alphabetically.
• Performance: On large tables, query performance for MIN() can be significantly improved by creating a B-tree index on the column being evaluated. The database can often find the minimum value by just reading the first entry in the index.

postgresql_useful_functions.htm