SQL Challenges-1: Third Highest Sale

Last update on December 20 2024 11:58:44 (UTC/GMT +8 hours)

From the following table, write a SQL query to find the third 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); 
INSERT INTO salemast (sale_id, employee_id, sale_date, sale_amt) VALUES ('3', '1003', '2012-04-10', 2500); 

SELECT  * FROM salemast;

SELECT DISTINCT sale_amt AS SecondHighestSale
FROM salemast
ORDER BY sale_amt DESC
LIMIT 1 OFFSET 1;

Sample Output:

SecondHighestSale|
-----------------|
             4500|

Solution-1:

SELECT MAX(sale_amt) AS SecondHighestSale
FROM (SELECT DISTINCT sale_amt
FROM salemast ORDER BY sale_amt DESC LIMIT 2 offset 1) AS sale;

Solution-2:

SELECT MAX(sale_amt) AS SecondHighestSale
FROM salemast
WHERE sale_amt<(SELECT MAX(sale_amt) FROM salemast);

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Combine two tables.
Next: N^th Highest Sale amount.