Home > DeveloperSection > Forums > Why does max function in sql return multiple values
Jayden Bell
Jayden Bell

Total Post:105

Points:739
Posted on    May-08-2013 1:30 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 1085  View(s)
Rate this:
Hi Everyone!

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

Thanks in advance!


AVADHESH PATEL

Total Post:604

Points:4228
Posted on    May-08-2013 8:17 AM

Hi Jayden!

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

Don't want to miss updates? Please click the below button!

Follow MindStick