SQL Server MAX()

SQL Server MAX() Function

The SQL Server MAX() function returns the maximum value in a set of values. This function is useful for finding the highest value in a column.

Syntax

SELECT MAX(column_name)
FROM table_name;

The MAX() function takes a single argument:

  • column_name: The name of the column for which to find the maximum value.

Example SQL Server MAX() Function Queries

Let's look at some examples of SQL Server MAX() function queries:

1. Basic MAX() Example

SELECT MAX(salary) AS highest_salary
FROM employees;

This query returns the highest value in the salary column of the employees table. The result will be:

highest_salary
--------------
100000

2. MAX() with a Condition

SELECT MAX(salary) AS highest_salary
FROM employees
WHERE department = 'Sales';

This query returns the highest value in the salary column for employees in the 'Sales' department. The result will be:

highest_salary
--------------
85000

3. MAX() with a Group By Clause

SELECT department, MAX(salary) AS highest_salary
FROM employees
GROUP BY department;

This query returns the highest value in the salary column for each department. The result will show the department and the corresponding highest_salary.

4. MAX() with a Variable

DECLARE @dept VARCHAR(50);
SET @dept = 'Engineering';
SELECT MAX(salary) AS highest_salary
FROM employees
WHERE department = @dept;

This query uses a variable to specify the department and then returns the highest salary for that department. The result will be:

highest_salary
--------------
95000

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the MAX() function.

Step 1: Creating a Table

This step involves creating a new table named employees to store some sample data.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

In this example, we create a table named employees with columns for id, name, department, and salary.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (id, name, department, salary) VALUES (1, 'John Doe', 'Engineering', 75000);
INSERT INTO employees (id, name, department, salary) VALUES (2, 'Jane Smith', 'Sales', 85000);
INSERT INTO employees (id, name, department, salary) VALUES (3, 'Jim Brown', 'Engineering', 95000);
INSERT INTO employees (id, name, department, salary) VALUES (4, 'Jake White', 'Sales', 80000);
INSERT INTO employees (id, name, department, salary) VALUES (5, 'Jill Green', 'HR', 70000);

Here, we insert data into the employees table.

Step 3: Using the MAX() Function

This step involves using the MAX() function to return the highest value in the salary column.

SELECT MAX(salary) AS highest_salary
FROM employees;

This query retrieves the highest value in the salary column for all rows in the employees table. The result will be:

highest_salary
--------------
95000

Conclusion

The SQL Server MAX() function is a powerful tool for returning the maximum value in a set of values. Understanding how to use the MAX() function and its syntax is essential for effective data analysis and processing in SQL Server.