ORDER BY and LIMIT

When you pull data from a database using SELECT, the database doesn’t guarantee any specific order for the results. The rows might come back in the order they were inserted, or in some seemingly random order dictated by how the database engines read from disk. To make data readable and useful, we need to sort it.

Sorting with ORDER BY

To sort your results, you use the ORDER BY clause. You place it at the end of your query, followed by the column you want to sort by.

SELECT name, price FROM products ORDER BY price;

By default, ORDER BY sorts in Ascending order (lowest to highest, A to Z). If you want to sort in Descending order (highest to lowest, Z to A), you add the DESC keyword.

SELECT name, price FROM products ORDER BY price DESC;

You can also sort by multiple columns! For example, ORDER BY category ASC, price DESC will first group items alphabetically by category, and then within each category, it will show the most expensive items first.

Restricting Results with LIMIT

Sometimes, you don’t need all the data. If you only want the “Top 10” or “First 5” results, you use the LIMIT clause. LIMIT goes at the very absolute end of your SQL statement.

SELECT * FROM products LIMIT 5;

When you combine ORDER BY and LIMIT, you unlock a very powerful pattern. You can easily find the largest, smallest, newest, or oldest items in your database.

For example, to find the single cheapest product in the store:

SELECT * FROM products ORDER BY price ASC LIMIT 1;

[!TIP] Not all database systems use the LIMIT keyword. While it works in PostgreSQL, MySQL, and SQLite, SQL Server uses TOP (e.g., SELECT TOP 10 * FROM products), and Oracle uses FETCH FIRST 10 ROWS ONLY. For this curriculum, we will stick to LIMIT.

Try combining both concepts in the lab to find the 3 most expensive products!