How to use MAX() for multiple occurrences of Max values in SQL

Question

Does the MAX() function return just the first occurrence of the maximum value it encounters? What happens if there are multiple occurrences across different rows of the same max value? Is there a way to get all these rows using the MAX() function? Please help!

Answer 1

MAX() is a scalar function returns a single value and not records so if you have multiple records that have the same value which is maximum, the following will still return only one value:

SELECT MAX(Value) FROM MyTable

If you want to get all records that have the maximum value, you can use

SELECT * FROM MyTable
WHERE Value = (SELECT MAX(Value) FROM MyTable)