Lesson 2 of 10
Sorting results
- 10 min
- 5 checks
- 3 hints
- Go to task
Goal
Sort with ORDER BY and cap the row count with LIMIT
Explanation
A database doesn't promise any row order: what comes first today might come later tomorrow. So when you need a particular order, ask for it explicitly. ORDER BY price DESC sorts from largest to smallest, and ASC sorts from smallest to largest, which is the default when you don't say anything.
LIMIT 3 takes the first three rows after sorting, so if you sort by price, most expensive first, you get the three most expensive products. Without ORDER BY, you get any three rows.
When you list two columns in ORDER BY, the database sorts the rows by the first one, and when two rows tie on it, it sorts them by the second. A sort direction applies only to the column it follows, so ORDER BY category, price DESC sorts the categories in ascending order and the prices within each category in descending order.
Your task
Show the name and price of the three most expensive products. If two products have the same price, put them in alphabetical order by name.
Checks
5 checks- Columns are name and price
- Only three rows
- Sorted most expensive first
- They really are the most expensive products
- The name breaks the tie between equal prices
Tables in this lesson
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT NOT NULL,
price INTEGER NOT NULL,
stock INTEGER NOT NULL
);
INSERT INTO products (name, category, price, stock) VALUES
('27-inch monitor', 'tech', 899, 4),
('Wireless headphones', 'tech', 249, 12),
('Keyboard', 'tech', 189, 0),
('Fast charger', 'tech', 79, 40),
('Office chair', 'home', 649, 3),
('Bookshelf', 'home', 249, 7),
('Desk lamp', 'home', 100, 18),
('Backpack', 'stationery', 180, 22),
('Notebook', 'stationery', 15, 200),
('Colored pencils', 'stationery', 25, 0),
('Dictionary', 'books', 95, 10),
('Novel', 'books', 45, 30);