lesson 08 Filtering & sorting · beginner

Sort & limit

Order your results and choose how many rows to return.

the idea

ORDER BY sorts the result; DESC reverses the order. LIMIT keeps the first n rows after sorting. When values tie, add a second sort key so “the first n” is well defined.

Dataset: Retail

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Sort all products by price. Run this. Without a filter, fees and adjustments can appear among the highest list prices.

   step-1.sql Run

   sql›

   SELECT stock_code, description, list_price
   FROM products
   WHERE list_price IS NOT NULL
   ORDER BY list_price DESC
   LIMIT 5;

2. 2

   Rank only merchandise. Run this. WHERE is_merchandise = 1 keeps goods only; stock_code breaks ties when prices match.

   step-2.sql Run

   sql›

   SELECT stock_code, description, list_price
   FROM products
   WHERE is_merchandise = 1
   ORDER BY list_price DESC, stock_code
   LIMIT 5;

3. 3

   Put it together. The lesson's pattern in one query.the pattern

   example.sql Run

   sql›

   SELECT stock_code, list_price
   FROM products
   ORDER BY list_price DESC, stock_code
   LIMIT 5;

common mistake

The most expensive stock codes include non-merchandise items such as bad-debt adjustments. Filter before ranking when the question is about merchandise.