Why does max function in sql return multiple value

Question

I would like to display the player with the highest salary.

select  max(Salary) as highest_salary, p.[Last name]
from tbl_PlayersTable as p, tbl_team as t
where p.Team = t.TeamID
and TeamID = 1000
Group by p.[Last name]

The output is:

highest_salary  Last Name
   8000          Bosh
   7000          Wade
   6000          James

I just want to display (8000 Bosh since he is the player with highest salary).

Answer 1

You did't need MAX nor GROUP BY, just use TOP 1 with ORDER BY Salary DESC. Something like this:

select TOP (1) Salary as highest_salary, p.[Last name]
from tbl_PlayersTable as p, tbl_team as t
where p.Team = t.TeamID
 and TeamID = 1000
ORDER BY Salary  DESC

Answer 2

You are grouping values there (see Group By in the end) and so your max function calculates Max value per group. If you wand an absolute max value, remove the grouping.

Answer 3

You will need to take the top 1 value

  select TOP (1) Salary as maxsalary, p.[Last name]
   from tbl_PlayersTable as p 
   Inner join  tbl_team as t on  p.Team = t.TeamID
  where TeamID = 1000
  ORDER BY Salary  DESC

Answer 4

No need for group by or even max:

select  top 1 Salary
,       [Last name]
from    tbl_PlayersTable
where   TeamID = 1000
order by
        salary desc

Answer 5

Because you use group by p.[Last name] so that the query will get max(Salary) for each distinct Last name it found. So if you want to get the max(Salary) base on all of Last name, you must remove group by