1

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

Gibron
  • 1,350
  • 1
  • 9
  • 28
user1954418
  • 963
  • 7
  • 21
  • 29

5 Answers5

6

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
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
2

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

Doan Cuong
  • 2,594
  • 4
  • 22
  • 39
  • it gives an error if i remove group by because i have a aggregate function in my select – user1954418 May 08 '13 at 07:31
  • if that's the case, use your current query as a `sub query` and wrap it by `select max(Salary) as highest_salary, p.[Last name] from` – Doan Cuong May 08 '13 at 07:41
1

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.

ElDog
  • 1,230
  • 1
  • 10
  • 21
  • it gives an error if i remove group by because i have a aggregate function in my select – user1954418 May 08 '13 at 07:30
  • See the top answer, it gives you a correct query, just replace LIMIT with SELECT TOP 1 as I mentioned there, this would be the syntax for sql server. – ElDog May 08 '13 at 07:32
  • @ElDog if he remove group by than he can not get last name he wolud get only Max Value – Amit Singh May 08 '13 at 07:38
1

No need for group by or even max:

select  top 1 Salary
,       [Last name]
from    tbl_PlayersTable
where   TeamID = 1000
order by
        salary desc
Andomar
  • 232,371
  • 49
  • 380
  • 404
0

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
Arun Kumar
  • 337
  • 3
  • 7
  • 20