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!
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)