SQL: Order of SQL command execution, ORDER BY, GROUP BY

created:

updated:

tags: database sql

Order of Execution of a Query

SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM tableA
    JOIN tableB
      ON tableA.column = tableB.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT;
  1. FROM and JOINs
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. DISTINCT
  7. ORDER BY
  8. LIMIT / OFFSET

ORDER BY

We can sort the result set by using ORDER BY cluase.

Order of SQL command

FROM -> SELECT -> ORDER BY.

With this order, we can define a column alias in the SELECT and we can use it in the ORDER BY clause.

Sort Order

By default, ORDER BY clause sorts the result set in the ascending order. We can specify the order with either ASC (ascending) and DESC (descending).

Sort Result Set by Multiple Columns

If there are mutliple columns that we’d like to sort the result set, ORDER BY clause sorts the first column first, and then sort the result again by the second column.

Example:

SELECT first_name, last_name
FROM customer
ORDER BY first_name ASC, last_name DESC;

It sorts the rows by values in the first_name first and sorts the rows by last_name column.

Sort Rows that Contains NULL

NULL indicates the missing data or unknown data at the time of recording in the database. When we sort rows that has NULL, we can specify the order of NULL with other non-null values by using NULLS FIRST or NULLS LAST option of the ORDER BY clause.

  • NULLS FIRST: places NULL before other non-null values
  • NULLS LAST: places NULL after other non-null values

Default Behaviour

  • If we use ASC for other non-null values, then ORDER BY sorts the rows and places NULL after the other values. This is similar to:
    SELECT num
FROM sort_demo
ORDER BY num NULLS LAST;
  • If we use DESC for other non-null values, then ORDER BY orts the rows and places NULL before the other values. This is similar to:
    SELECT num
FROM sort_demo
ORDER BY num DESC NULLS FIRST;
  • We can override this default behaviour by specifying NULLS FIRST or NULLS LAST in the ORDER BY clause.

Notes

When we use LIMIT or FETCH clause, we must use ORDER BY together to return consistent result set. By default, the order of rows in the table is unspecified.

GROUP BY

The GROUP BY clause divides the rows returned from the SELECT statement into groups. For each group, you an apply an aggregate function e.g., SUM() to calculate the sum of items or COUNT() to get the number of items in the groups.

  • If an aggregate function is not used with GROUP BY, it can removes duplicate rows from the result set like DISTINCT
# query to get the total amount that each customer has been paid
SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id;

We can use ORDER BY and GROUP BY together:

SELECT customer_id, SUM (amount)
FROM payment
GROUP BY customer_id
ORDER BY SUM (amount) DESC;

We can also use GROUP BY with JOIN clause:

# query that joins the payment table with customer table and group customers by their names
# and sorted by total payment of each customer
SELECT first_name || ' ' || last_name full_name,
       SUM (amount) amount
FROM payment
INNER JOIN customer USING (customer_id)
GROUP BY full_name
ORDER BY amount DESC;

We can use GROUP BY with multiple columns

# `GROUP BY` divides the rows in the payment table by the values in the customer_id and staff_id columns.
# For each (customer_id, staff_id) group, SUM() calculates the total amount.
SELECT customer_id, staff_id, SUM (amount)
FROM payment
GROUP BY staff_id, customer_id
ORDER BY customer_id;

We can use GROUP BY with a date column:

# use DATE() function to convert timestamps to dates
# then group payments by result date
SELECT DATE(payment_date) paid_date,
       SUM(amount) sum
FROM payment
GROUP BY DATE(payment_date);

Reference


Copyright (c) 2025 Sunmin Kim