Most Expensive

Description


Mr. Cash wants to keep track of his expenses, so he has prepared a list of all the products he bought this month. Now he is interested in finding the product on which he spent the largest amount of money. If there are products that cost the same amount of money, he’d like to find the one with the lexicographically smallest name.

Note: String A is lexicographically smaller than string B either if A is a prefix of B (and A ≠ B), or if there exists such index i (0 ≤ i < min(A.length, B.length)), that Ai < Bi, and for any j (0 ≤ j < i) Aj = Bj. The lexicographic comparison of strings is implemented by operator < in modern programming languages.

The list of expenses is stored in a table Products which has the following columns:

  • id: unique product id;
  • name: the unique name of the product;
  • price: the price for one item;
  • quantity: the number of items bought.

The resulting table should contain one row with a single column: the product with the lexicographically smallest name on which Mr. Cash spent the largest amount of money.

The total amount of money spent on a product is calculated as price \* quantity.

Example

  • For the following table Products
id name price quantity
1 MacBook Air 1500 1
2 Magic Mouse 79 1
3 Spray cleaner 10 3

the output should be

name
MacBook Air
  • For the following table Products
id name price quantity
1 Tomato 10 4
2 Cucumber 8 5
3 Red Pepper 20 2
4 Feta 40 1

the output should be

name
Cucumber

While the total cost for each product was 40, Cucumber has the lexicographically smallest name.

  • [execution time limit] 10 seconds (mysql)

Solution


1
2
3
4
5
/*Please add ; after each select statement*/
CREATE PROCEDURE mostExpensive()
BEGIN
	SELECT name from Products ORDER BY price*quantity DESC, name LIMIT 1;
END