I have a table showing sales_manID, name, city, commission. The problem is:
- Write a SQL to select the maximum value of commission and display the salesman_id, name and city.
Maximum value, so do you want the total commission for a sales man, or the 1 largest commission.
E.g Sales Man 1 has 2 commissions at $1000 each
Sales Man 2 has 1 commission at $1500
What do you want to see, sales man 1 with a commission of $2000 or sales man 2 with a commission of $1500.
Option 1 for the $2000 result, go look at the aggregate function SUM.
Option 2 for the $1500 result look at TOP and ORDER BY
Thank you! I am looking for it to show just the men with the highest % commission, in this case 0.15 (decimal as percent). So I can do the exercise where I specify 0.15, but not if I'm just trying for MAX.
Keep in mind I'm a newbie :)
CREATE TABLE #commission (salesman_id int, name varchar(10), city varchar(10), commission decimal(5,2)) INSERT INTO #commission VALUES(1,'Ant','Manchester',15.0), (2,'Amonds','NewYork',10.5) SELECT TOP 1 salesman_id, name, city, commission FROM #commission ORDER BY commission DESC
15 People are following this question.