﻿ SQL Challenges-1: Third Highest Sale - w3resource

# SQL Challenges-1: Third Highest Sale

## SQL Challenges-1: Exercise-2 with Solution

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

Input:

Table: salesemast

Structure:

FieldTypeNullKeyDefaultExtra
sale_idint(11)YES
employee_idint(11)YES
sale_datedateYES
sale_amtint(11)YES

Data:

sale_idemployee_idsale_datesale_amt
110002012-03-084500
210012012-03-095500
310032012-04-103500
310032012-04-102500

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:

Previous: Combine two tables.
Next: Nth Highest Sale amount.

﻿

## SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

```SELECT...
LIMIT y OFFSET x
```

Oracle, DB2 and MSSQL supports the standard windowing functions:

```SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
columns
FROM tablename
) AS foo
WHERE rownumber <= n
```

Database: SQL

Ref: https://bit.ly/3zPxcD8