VisaSpark
Sign in
beginner24 min

Filtering and Sorting Rows: WHERE and ORDER BY

Narrowing down a table to just the rows you want with WHERE, and controlling their order with ORDER BY.

What you'll learn

  • Write SELECT queries that choose specific columns
  • Filter rows using WHERE with comparison operators
  • Sort query results with ORDER BY, ascending or descending

Prerequisites

Explanation

A SELECT statement always answers two questions: which columns do you want, and which rows? So far you've selected every row of a table. In real use, you almost always want a subset.

WHERE narrows the rows. SELECT title, price FROM books WHERE genre = 'Fiction'; only returns rows where that condition is true. WHERE supports the comparisons you'd expect: =, != (or <>), <, >, <=, >=, and you can combine conditions with AND / OR. Text values are wrapped in single quotes ('Fiction'); numbers are not.

ORDER BY controls the sequence of results. By default, SQL makes no promise about what order rows come back in — even if it looks consistent while you're testing. If order matters to your result (a leaderboard, "newest first", "cheapest first"), you must say so explicitly: ORDER BY price ASC sorts cheapest-to-most-expensive, ORDER BY price DESC sorts the opposite way. ASC (ascending) is the default if you omit the direction.

You can combine both freely: filter first, then sort what's left. SELECT title, price FROM books WHERE genre = 'Nonfiction' ORDER BY price DESC; finds every nonfiction book, then lists them most expensive first.

One subtlety worth internalizing early: when several rows tie on your ORDER BY column, SQL doesn't guarantee which one comes first between them unless you add a second sorting column as a tiebreaker (for example, ORDER BY published_year DESC, title ASC). For the exercises in this lesson, the data has been chosen so ties don't come up — but it's a habit worth having once your own sort columns aren't guaranteed unique.

WHERE and ORDER BY together are the two tools you'll reach for constantly: WHERE decides what counts, ORDER BY decides what order you see it in.

Example

Filter to Nonfiction books, then sort them most expensive first.

SELECT title, price
FROM books
WHERE genre = 'Nonfiction'
ORDER BY price DESC;

Try it yourself

Try changing the genre or flipping ASC/DESC, then press Run to see the order change.

Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.

Loading editor…

Guided exercise

Guided exercise

Complete the query so it returns the title and price of every book with a price under 15.00.

Checks: Returns exactly the books priced under $15.00

Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.

Loading editor…

Stuck? Get a hint.

Independent exercise

Independent exercise

Write a query that returns the title and published_year of every Fiction book, sorted from newest to oldest (order matters for this check).

Checks: Returns Fiction books ordered from newest to oldest (row order matters)

Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.

Loading editor…

Stuck? Get a hint.

Common mistakes

  • Forgetting single quotes around text values in WHERE, e.g. writing WHERE genre = Fiction instead of WHERE genre = 'Fiction'.
  • Assuming row order is guaranteed without an explicit ORDER BY — SQL makes no such promise on its own.
  • Mixing up ASC and DESC, ending up with the smallest or oldest value first when the largest or newest was wanted.
  • Using = to compare against a range of values instead of operators like < or BETWEEN.

Knowledge check

Knowledge check

1. What does the WHERE clause do in a SELECT statement?
2. Without an ORDER BY clause, what can you assume about the order of returned rows?
3. Which clause sorts books from most expensive to least expensive?
4. In `WHERE price < 15.00`, what kind of rows are kept?

Takeaway

WHERE decides which rows make the cut, and ORDER BY decides the sequence you see them in — neither is assumed unless you write it.

Summary

SELECT queries narrow rows with WHERE using comparison operators, and control result ordering with ORDER BY (ASC or DESC). Row order is never guaranteed without an explicit ORDER BY, which matters whenever a query's meaning depends on sequence.

References

Your notes

Notes save automatically.

Finished this lesson?

Mark it complete to track your progress and schedule a future review.

Next: Changing Data: INSERT, UPDATE, and DELETE →