In this lesson, you will learn how to organize your query results and control the volume of data returned by the database.
Organizing Your Output
When you perform a SELECT statement, the database returns rows in whatever order it finds them, which might not be predictable. To make your data human-readable, we use the ORDER BY clause. This allows you to sort your results based on the values in specific columns.
You can sort in ascending order (the default, using ASC) or descending order (using DESC).
SELECT product_name, price
FROM products
ORDER BY price DESC;
In this example, the database will return the products starting with the most expensive item at the top and moving down to the cheapest.
Managing Result Volume
Sometimes, you don't need to see every single row in a table. Perhaps you only want to see the top five performers, or you are building a "page" of results to display on a website. This is where LIMIT comes in.
The LIMIT clause acts as a cutoff point. It tells the database to stop searching once it has retrieved a specific number of records.
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 5;
By combining ORDER BY and LIMIT, we can retrieve the "Top 5" most expensive products. If we wanted to see the next five, we would use a companion clause called OFFSET, which instructs the database to skip a specific number of rows before it begins counting toward the LIMIT.
Why This Matters
Remember our earlier discussions about the anatomy of a SELECT statement and filtering with predicates. While predicates (like WHERE) determine which rows satisfy our criteria, ORDER BY and LIMIT determine the presentation of that subset. This is essential for performance and usability; fetching a million rows when you only need the first ten would be a waste of memory and processing power. By mastering these controls, you ensure your queries provide exactly the information requested in the most efficient manner possible.
Key Takeaway: Use ORDER BY to dictate the sequence of your results and LIMIT to restrict the volume, ensuring your data is both readable and efficiently retrieved.