SQL ORDER BY, LIMIT and GROUP BY

Sort results, return only the top rows, and summarise data with COUNT, SUM, AVG and GROUP BY.

Lesson 5 of 102Beginner19 min read

A database doesn't promise to return rows in any particular order. If you want the cheapest products first, the newest customers at the top or the five best sellers, you have to ask for it. ORDER BY sorts the result and LIMIT keeps just the first few rows.

Often you don't want individual rows at all, but a summary: how many products are there, what's the average price, how much stock does each category hold? Aggregate functions such as COUNT, SUM and AVG turn many rows into one answer, and GROUP BY gives you one answer per group.

These clauses are what turn a database into a reporting tool, and they're among the most useful SQL skills you can have.

Sorting with ORDER BY

ORDER BY goes after WHERE (if there is one) and names the column to sort by:

SQL
SELECT name, price FROM products
ORDER BY price;          -- cheapest first
  • ASC sorts ascending, smallest to largest or A to Z. It's the default.
  • DESC sorts descending: ORDER BY price DESC puts the most expensive first.
  • Sort by several columns by separating them with commas. The second column breaks ties in the first: ORDER BY city, name.

LIMIT: just the top rows

LIMIT sets the maximum number of rows returned. Combined with ORDER BY, it answers "top N" questions:

SQL
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 3;

LIMIT works in MySQL, PostgreSQL and SQLite. SQL Server uses SELECT TOP 3 ... instead, and some databases use FETCH FIRST 3 ROWS ONLY. The idea is the same.

Aggregate functions

FunctionReturns
COUNT(*)number of rows
SUM(col)total of a number column
AVG(col)average of a number column
MIN(col) / MAX(col)smallest / largest value

An aggregate squashes all the matching rows into one value. You can use several at once and combine them with WHERE, which filters rows before they're counted: SELECT COUNT(*) FROM customers WHERE city = 'Leeds' returns 2.

GROUP BY and HAVING

GROUP BY splits the rows into groups that share a value, then runs the aggregate once per group:

SQL
SELECT category, COUNT(*) AS products
FROM products
GROUP BY category;

Every column in the SELECT list must either be in the GROUP BY or be inside an aggregate function.

To filter groups rather than rows, use HAVING: HAVING COUNT(*) > 1 keeps only categories with more than one product. Remember: WHERE filters rows before grouping, HAVING filters groups after.

Pages of results with OFFSET

LIMIT gives the first rows; OFFSET skips some rows first, which is how page 2, page 3 and so on are produced:

SQL
SELECT name, price
FROM products
ORDER BY price, id
LIMIT 2 OFFSET 2;     -- rows 3 and 4: the second page of two

The rule for page n with size rows per page is OFFSET (n - 1) * size. Always sort by something unique, here price and then id as a tie-breaker, so a row can't appear on two pages or on none when prices are equal.

Sorting by expressions and aliases

ORDER BY can sort by a calculation or by an alias from SELECT, because sorting happens after the columns are worked out:

SQL
SELECT name, price * stock AS stock_value
FROM products
ORDER BY stock_value DESC;

You can also sort by a column that isn't selected, such as ORDER BY joined while showing only name. Sorting by column position, ORDER BY 2, works too, but it breaks silently when someone changes the column list, so names are safer.

SQL ORDER BY and GROUP BY Syntax

ORDER BY, LIMIT and GROUP BY

SELECT column, AGGREGATE(column) AS alias
FROM table_name
WHERE condition
GROUP BY column
HAVING AGGREGATE(column) > value
ORDER BY column DESC
LIMIT n;

SQL ORDER BY and GROUP BY Summary

  • ORDER BY col sorts ascending; add DESC for descending. Several columns break ties.
  • LIMIT n returns at most n rows; use it with ORDER BY for "top N" lists.
  • COUNT, SUM, AVG, MIN and MAX summarise many rows into one value.
  • GROUP BY col gives one summary row per distinct value of col.
  • WHERE filters rows before grouping; HAVING filters groups after.

SQL ORDER BY and GROUP BY Examples

Each example shows the code, the output it produces and what each part does.

Example 1

Most expensive first

SQL
SELECT name, price
FROM products
ORDER BY price DESC;
Output
name         | price
-------------+------
Backpack     | 34.99
Desk lamp    | 24.50
Water bottle | 12.00
Lunch box    | 8.75
Notebook     | 3.50
Pen          | 1.20
Line-by-line explanation
  1. Line 3DESC puts the largest price at the top.
Example 2

The three cheapest products

SQL
SELECT name, price
FROM products
ORDER BY price
LIMIT 3;
Output
name      | price
----------+------
Pen       | 1.20
Notebook  | 3.50
Lunch box | 8.75
Line-by-line explanation
  1. Line 3Sort from cheapest (ASC is the default).
  2. Line 4Then keep only the first three rows.
Example 3

A one-row summary

SQL
SELECT COUNT(*)             AS products,
       ROUND(AVG(price), 2) AS average_price,
       MAX(price)           AS highest_price,
       SUM(stock)           AS items_in_stock
FROM products;
Output
products | average_price | highest_price | items_in_stock
---------+---------------+---------------+---------------
6        | 14.16         | 34.99         | 497
Line-by-line explanation
  1. Line 1COUNT(*) counts all six rows.
  2. Line 2The average of the six prices, rounded to 2 decimals.
  3. Line 4The stock values added together.
Example 4

Stock per category

SQL
SELECT category,
       COUNT(*)   AS products,
       SUM(stock) AS total_stock
FROM products
GROUP BY category
ORDER BY products DESC, category;
Output
category   | products | total_stock
-----------+----------+------------
Kitchen    | 2        | 40
Stationery | 2        | 420
Bags       | 1        | 15
Home       | 1        | 22
Line-by-line explanation
  1. Line 5One result row per category.
  2. Line 6Sort by the product count, highest first; categories with the same count are sorted A to Z.

Common SQL ORDER BY and GROUP BY Mistakes

Using WHERE to filter a group

Don't
SELECT category, COUNT(*) FROM products
WHERE COUNT(*) > 1
GROUP BY category;
Do
SELECT category, COUNT(*) FROM products
GROUP BY category
HAVING COUNT(*) > 1;

Aggregates can't be used in WHERE, because WHERE runs before the rows are grouped.

Selecting a column that isn't grouped

Don't
SELECT category, name, COUNT(*)
FROM products
GROUP BY category;
Do
SELECT category, COUNT(*)
FROM products
GROUP BY category;

Each group has several names, so the database can't know which one to show. Most databases reject the first query.

Clauses in the wrong order

Don't
SELECT name FROM products
LIMIT 3
ORDER BY price;
Do
SELECT name FROM products
ORDER BY price
LIMIT 3;

SQL ORDER BY and GROUP BY in Real Projects

Reports and dashboards

"Top 10 best sellers", "orders per day" and "average basket value" are all GROUP BY, ORDER BY and LIMIT queries. Business dashboards are often just a collection of queries like these, run every few minutes and drawn as charts.

SQL ORDER BY and GROUP BY FAQ

Does GROUP BY sort the results?

Not reliably. Some databases happen to return groups in order, but the SQL standard doesn't promise it and the order can change with indexes or versions. Add an explicit ORDER BY whenever the order of groups matters.

Can I use HAVING without GROUP BY?

Yes. Without GROUP BY, the whole filtered table counts as one group, so SELECT COUNT(*) FROM products HAVING COUNT(*) > 5 returns the count only when there are more than five products. It's uncommon but valid.

Is COUNT(1) faster than COUNT(*)?

No. Both count rows, and databases treat them the same way. COUNT(*) is the standard and clearest form. COUNT(column) is different, because it skips rows where that column is NULL.

How do I sort text without caring about capital letters?

Sort by a lowercase version, ORDER BY LOWER(name), which works everywhere. Many databases also offer case-insensitive collations, such as COLLATE NOCASE in SQLite, and MySQL's default collations already ignore case.

How do I get the top row for each group, such as the cheapest product per category?

GROUP BY returns one summary row per group, not a whole row. Use a window function such as ROW_NUMBER() OVER (PARTITION BY category ORDER BY price) and keep the rows numbered 1, as the window functions lesson shows.

SQL ORDER BY and GROUP BY Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the query so the newest customers come first.

SELECT name, joined
FROM customers
 joined ;
Predict the outputeasy

What does this return?

SELECT COUNT(*) FROM customers WHERE city = 'Leeds';
Multiple choicemedium

Which clause keeps only the groups whose total stock is over 100?

Predict the outputmedium

Which product does this return?

SELECT name FROM products
ORDER BY stock DESC
LIMIT 1;
Multiple choicehard

Which query lists categories that have more than one product?

SQL ORDER BY and GROUP BY Quiz

5 questions to check what you learned.

  1. What is the default sort direction of ORDER BY?

    • Descending
    • Ascending
    • Random
    • The order rows were added

  2. What does GROUP BY category do?

    • Sorts the rows by category
    • Removes the category column
    • Deletes duplicate categories from the table
    • Creates one result row per category, for use with aggregate functions

  3. Which function returns the number of rows?

    • SUM()
    • TOTAL()
    • COUNT()
    • NUMBER()

  4. In which order must these clauses appear?

    • SELECT, FROM, WHERE, GROUP BY, ORDER BY
    • SELECT, WHERE, FROM, ORDER BY
    • SELECT, FROM, ORDER BY, WHERE
    • FROM, SELECT, GROUP BY, WHERE

  5. What does ORDER BY price DESC do?

    • Sorts cheapest first
    • Sorts most expensive first
    • Removes duplicate prices
    • Groups by price

Related SQL topics

03WHERE (Filtering)Return only the rows you need with comparisons, AND, OR, NOT, IN, BETWEEN, LIKE and IS NULL.02SELECTRead exactly the columns you need, rename them, calculate new values and remove duplicates.12JOINCombine rows from related tables with INNER JOIN and LEFT JOIN, using keys and table aliases.
Esc
↑↓ to navigateEnter to open