Question

From the following mySql query create a function that returns a n^th value of a table:

SELECT name, (SUM(price * quantity)) AS TotalPrice

FROM Items NATURAL JOIN Inventory

GROUP BY name

ORDER BY (TotalPrice) DESC

LIMIT 1, OFFSET 1;

Shema:

Inventory(name: String,item:String,quantity:integer)

Items(item: String, brand: String, price:double)

Answer 1

MySQL Function to return the nth value of table:

Syntax to create MySQL Function:
CREATE FUNCTION function_name(param1,param2,…)
RETURNS datatype
BEGIN
    SQL statements
END;

MySQL Function to return the nth value

CREATE FUNCTION GET_NthVal(n INT) RETURNS DOUBLE
BEGIN
   /*VARIABLE DECLARATION TO STORE Nth VALUE*/
   DECLARE NVal DOUBLE DEFAULT 0;

  DECLARE tName VARCHAR(25);

   SELECT name, (SUM(price * quantity)) AS TotalPrice INTO tName,NVal
   FROM Items NATURAL JOIN Inventory
   GROUP BY name
   ORDER BY (TotalPrice) DESC
   LIMIT 1, OFFSET n-1;

   RETURN NVal;
END;//

The function named 'GET_NthVal' returns a 'double' value which is the value(total price) of the nth record of the given query.
TotalPrice of nth record will be stored to the variale 'NVal' and returned to the caller funtion/ place.
Here OFFSET is set to n-1 and LIMIT to 1 and it is to skip the first n-1 records and starts from n th record. In this way you will get the nth value of any table/ result set.

Kindly Note:
Feel free to comment if you have any doubts regarding the solution.