MySQL Northwind database, Products table : Display Product list of twenty most expensive products

MySQL Northwind database: Exercise-8 with Solution

8. Write a MySQL query to get Product list (name, unit price) of twenty most expensive products.

Code:

-- This SQL query retrieves distinct ProductName and UnitPrice combinations from the Products table, selecting the top 20 most expensive products based on their UnitPrice, and orders the results by UnitPrice in descending order.

SELECT DISTINCT ProductName as Twenty_Most_Expensive_Products, UnitPrice -- Selects distinct ProductName and UnitPrice combinations from the Products table, aliasing ProductName as Twenty_Most_Expensive_Products
FROM Products AS a -- Specifies the table from which to retrieve data, aliasing it as 'a'
WHERE 20 >= (SELECT COUNT(DISTINCT UnitPrice) -- Filters the rows to include only the top 20 most expensive products based on their UnitPrice
                    FROM Products AS b -- Subquery: Specifies the table from which to retrieve data, aliasing it as 'b'
                    WHERE b.UnitPrice>= a.UnitPrice) -- Subquery: Counts the number of distinct UnitPrice values greater than or equal to the UnitPrice of the current row in table 'a'
ORDER BY UnitPricedesc; -- Orders the result set by the UnitPrice column in descending order

Explanation:

• The SELECT statement retrieves distinct ProductName and UnitPrice combinations from the Products table, aliasing ProductName as Twenty_Most_Expensive_Products.
• The WHERE clause filters the rows to include only the top 20 most expensive products based on their UnitPrice. This is achieved by comparing the count of distinct UnitPrice values greater than or equal to the UnitPrice of the current row in table 'a' to 20.
• The subquery(SELECT COUNT(DISTINCT UnitPrice) FROM Products AS b WHERE b.UnitPrice>= a.UnitPrice) calculates the number of distinct UnitPrice values greater than or equal to the UnitPrice of the current row in table 'a'.
• The ORDER BY clause orders the result set by the UnitPrice column in descending order.

Structure of Products table:

Sample records of Products Table:

+-----------+---------------------------------+------------+------------+---------------------+-----------+--------------+--------------+--------------+--------------+
| ProductID | ProductName                     | SupplierID | CategoryID | QuantityPerUnit     | UnitPrice | UnitsInStock | UnitsOnOrder | ReorderLevel | Discontinued |
+-----------+---------------------------------+------------+------------+---------------------+-----------+--------------+--------------+--------------+--------------+
|         1 | Chai                            |          1 |          1 | 10 boxes x 20 bags  |   18.0000 |           39 |            0 |           10 |              |
|         2 | Chang                           |          1 |          1 | 24 - 12 oz bottles  |   19.0000 |           17 |           40 |           25 |              |
|         3 | Aniseed Syrup                   |          1 |          2 | 12 - 550 ml bottles |   10.0000 |           13 |           70 |           25 |              |
|         4 | Chef Anton's Cajun Seasoning    |          2 |          2 | 48 - 6 oz jars      |   22.0000 |           53 |            0 |            0 |              |
|         5 | Chef Anton's Gumbo Mix          |          2 |          2 | 36 boxes            |   21.3500 |            0 |            0 |            0 | ☺            |
|         6 | Grandma's Boysenberry Spread    |          3 |          2 | 12 - 8 oz jars      |   25.0000 |          120 |            0 |           25 |              |
|         7 | Uncle Bob's Organic Dried Pears |          3 |          7 | 12 - 1 lb pkgs.     |   30.0000 |           15 |            0 |           10 |              |
|         8 | Northwoods Cranberry Sauce      |          3 |          2 | 12 - 12 oz jars     |   40.0000 |            6 |            0 |            0 |              |
|         9 | Mishi Kobe Niku                 |          4 |          6 | 18 - 500 g pkgs.    |   97.0000 |           29 |            0 |            0 | ☺            |
|        10 | Ikura                           |          4 |          8 | 12 - 200 ml jars    |   31.0000 |           31 |            0 |            0 |              |
|        11 | Queso Cabrales                  |          5 |          4 | 1 kg pkg.           |   21.0000 |           22 |           30 |           30 |              |
|        12 | Queso Manchego La Pastora       |          5 |          4 | 10 - 500 g pkgs.    |   38.0000 |           86 |            0 |            0 |              |
|        13 | Konbu                           |          6 |          8 | 2 kg box            |    6.0000 |           24 |            0 |            5 |              |
|        14 | Tofu                            |          6 |          7 | 40 - 100 g pkgs.    |   23.2500 |           35 |            0 |            0 |              |
|        15 | Genen Shouyu                    |          6 |          2 | 24 - 250 ml bottles |   15.5000 |           39 |            0 |            5 |              |
....
+-----------+---------------------------------+------------+------------+---------------------+-----------+--------------+--------------+--------------+--------------+

Sample Output:

Twenty_Most_Expensive_Products				UnitPrice
Cte de Blaye						263.5000
Thringer Rostbratwurst					123.7900
Mishi Kobe Niku						97.0000
Sir Rodney's Marmalade					81.0000
Carnarvon Tigers					62.5000
Raclette Courdavault					55.0000
Manjimup Dried Apples					53.0000
Tarte au sucre						49.3000
Ipoh Coffee						46.0000
Rssle Sauerkraut					45.6000
Schoggi Schokolade					43.9000
Vegie-spread						43.9000
Northwoods Cranberry Sauce				40.0000
Alice Mutton						39.0000
Gnocchi di nonna Alice					38.0000
Queso Manchego La Pastora				38.0000
Gudbrandsdalsost					36.0000
Mozzarella di Giovanni					34.8000
Camembert Pierrot					34.0000
Wimmers gute Semmelkndel				33.2500
Perth Pasties						32.8000
Mascarpone Fabioli					32.0000

MySQL Online Editor:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.