SQL Exercises: Salesman details by smallest ID along with order date

SQL Formatting Output: Exercise-7 with Solution

Write a SQL statement to make a report with salesman ID, order date and highest purchase amount in such an arrangement that, the smallest salesman ID will come first along with their smallest order date.

Sample table: orders

Sample Solution :

SELECT salesman_id,ord_date,MAX(purch_amt) 
FROM orders 
GROUP BY salesman_id,ord_date 
ORDER BY salesman_id,ord_date;

Output of the Query:

salesman_id	ord_date	max
5001		2012-04-25	3045.60
5001		2012-07-27	2400.60
5001		2012-09-10	5760.00
5001		2012-10-05	65.26
5002		2012-06-27	250.45
5002		2012-09-10	948.50
5002		2012-10-05	150.50
5003		2012-08-17	110.50
5003		2012-10-10	2480.40
5005		2012-09-10	270.65
5006		2012-10-10	1983.43
5007		2012-08-17	75.29

Explanation :

Pictorial presentation :

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a SQL statement to display the customer name, city, and grade, etc. and the display will be arranged according to the smallest customer ID.
Next: Write a SQL statement to display customer name, city and grade in such a manner that, the customer holding highest grade will come first.