question

amonds avatar image
amonds asked

I need to show 3 columns based on a 4th column pleas

I have a table showing sales_manID, name, city, commission. The problem is:

  1. Write a SQL to select the maximum value of commission and display the salesman_id, name and city.

Thank you!

query
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

amonds avatar image
amonds answered

@anthony.green

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

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·

Thank you so much!

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.