A database returns rows in no promised order. If the order matters, ask for it with ORDER BY:
SELECT name, price
FROM products
ORDER BY price;
That sorts from the lowest price to the highest. Sorting is ascending by default: A to Z, small to large, old to new. Add DESC for descending order:
ORDER BY price DESC
Sorting by several columns
List more than one column, and the second is used to order rows that are equal on the first:
SELECT name, category, price
FROM products
ORDER BY category, price DESC;
This groups the products by category, and within each category shows the most expensive first.
LIMIT
LIMIT cuts the result off after a number of rows. Together with ORDER BY it answers "top N" questions:
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;
The order of the parts
The parts of a query must be written in a fixed order:
SELECTFROMWHEREORDER BYLIMIT
Putting WHERE after ORDER BY is an error.
Tables in the practice database
| Table | Columns |
|---|---|
customers | id, name, city, joined |
products | id, name, category, price, stock |
orders | id, customer_id, product_id, quantity, ordered |
Every run starts from a fresh copy of the database, so nothing you do can break it.
Your Task
- Find the three cheapest products that are in stock (a
stockgreater than 0). - Return their
nameandprice, cheapest first.
- The query uses
ORDER BYandLIMIT - Exactly three rows come back, with the columns
nameandprice - They are the three cheapest products in stock, cheapest first
Lesson complete!
Next Lesson: Searching Text and Ranges