SQL Basics · Lesson 3 of 9

Sorting and Limiting

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:

  1. SELECT
  2. FROM
  3. WHERE
  4. ORDER BY
  5. LIMIT

Putting WHERE after ORDER BY is an error.

Tables in the practice database
TableColumns
customersid, name, city, joined
productsid, name, category, price, stock
ordersid, 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

  1. Find the three cheapest products that are in stock (a stock greater than 0).
  2. Return their name and price, cheapest first.
  • The query uses ORDER BY and LIMIT
  • Exactly three rows come back, with the columns name and price
  • They are the three cheapest products in stock, cheapest first