SQL Exercises: Find highest purchase amount by the customer from the salesman with a specified ID range
SQL Aggregate Functions: Exercise-17 with Solution
Write a SQL statement to find the highest purchase amount with their ID, for only those salesmen whose ID is within the range 5003 and 5008.
Sample table: orders
SELECT salesman_id,MAX(purch_amt) FROM orders GROUP BY salesman_id HAVING salesman_id BETWEEN 5003 AND 5008;
Output of the Query:
salesman_id max 5005 270.65 5003 2480.40 5007 75.29 5006 1983.43
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a SQL statement to display customer details (ID and purchase amount) whose IDs are within the range 3002 and 3007 and highest purchase amount is more than 1000.
Next: Write a SQL statement that counts all orders for a date August 17th, 2012.
What is the difficulty level of this exercise?