MAX() Function in SQL Server
The MAX() function in SQL Server is used to return the maximum value of a specified column in a table. It can be used with numeric, date/time, and character data types.
Syntax:
SELECT MAX(column_name) FROM table_name;
Example: Consider a table named "students" with columns "name" and "age". To find the maximum age of all the students in the table, we can use the following query:
SELECT MAX(age) FROM students;
This will return the maximum age value from the "age" column of the "students" table.
Another example: Consider a table named "sales" with columns "product_name" and "sales_amount". To find the maximum sales amount of all the products in the table, we can use the following query:
SELECT MAX(sales_amount) FROM sales;
This will return the maximum sales amount value from the "sales_amount" column of the "sales" table.
Multiple methods to use MAX() function: 1. Using MAX() function with GROUP BY clause: We can use the MAX() function with the GROUP BY clause to find the maximum value of a column for each group of rows in a table. For example, to find the maximum sales amount for each product in the "sales" table, we can use the following query:
SELECT product_name, MAX(sales_amount) FROM sales GROUP BY product_name;
- Using MAX() function with WHERE clause: We can use the MAX() function with the WHERE clause to find the maximum value of a column that satisfies a specific condition. For example, to find the maximum age of all the male students in the "students" table, we can use the following query:
SELECT MAX(age) FROM students WHERE gender = 'male';