SQL Challenges-1: N^th Highest Sale amount

SQL Challenges-1: Exercise-3 with Solution

From the following table, write a SQL query to find the N^th highest sale. Return sale amount.

Input:

Table: salesemast

Structure:

Data:

Sample Solution:

SQL Code(MySQL):

CREATE TABLE IF NOT EXISTS salemast(sale_id int, employee_id int, sale_date date, sale_amt int);
TRUNCATE TABLE salemast;
INSERT INTO salemast (sale_id, employee_id, sale_date, sale_amt) VALUES ('1', '1000', '2012-03-08', 4500);
INSERT INTO salemast (sale_id, employee_id, sale_date, sale_amt) VALUES ('2', '1001', '2012-03-09', 5500);
INSERT INTO salemast (sale_id, employee_id, sale_date, sale_amt) VALUES ('3', '1003', '2012-04-10', 3500); 
SELECT * FROM salemast;

Sample Output:

getNthHighestSaleAmt(3)|
-----------------------|
                   3500|

Solution-1:

# Solution
USE ‘temp';
DROP function IF EXISTS 'getNthHighestSaleAmt';
DELIMITER $$
USE 'temp'$$
# -------------------------------
CREATE FUNCTION 'getNthHighestSaleAmt' (N INT) RETURNS int(11)
BEGIN
  SET N = N-1;
  RETURN (
        SELECT DISTINCT sale_amt FROM salemast
        ORDER BY sale_amt DESC
        LIMIT 1 OFFSET N
    );
END$$
DELIMITER ;
# To Execute the function:
SELECT getNthHighestSaleAmt(3);

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Third Highest Sale.
Next: Consecutive Numbers.