Sorting and Limiting Results

While the WHERE clause filters which rows are retrieved, the ORDER BY and LIMIT clauses determine how those retrieved rows are presented and how many are visible.

1. Sorting Data (ORDER BY)

By default, the order of rows returned by a SELECT statement is arbitrary (often based on insertion order). The ORDER BY clause is used to explicitly sort the result set based on the values in one or more columns.

Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

A. Sorting Order Keywords

• ASC (Ascending): Sorts the results from the lowest value to the highest (A to Z, 0 to 9, oldest date to newest date). This is the default behavior if no keyword is specified.
• DESC (Descending): Sorts the results from the highest value to the lowest (Z to A, 9 to 0, newest date to oldest date).

Execution Example 1: Simple Sorting

To list all employees sorted by their salary, from the lowest paid to the highest paid:

SELECT first_name, last_name, salary
FROM Employees
ORDER BY salary ASC; 
-- ASC is optional here, as it's the default

Execution Example 2: Reverse Sorting (Descending)

To list employees based on their hire date, showing the most recently hired employee first:

SELECT first_name, hire_date
FROM Employees
ORDER BY hire_date DESC;

Execution Example 3: Sorting by Multiple Columns

When sorting by multiple columns, the second column acts as a tie-breaker for any identical values in the first column.

To sort first by salary (descending) and then, for employees with the same salary, sort by last name (ascending):

SELECT last_name, salary
FROM Employees
ORDER BY salary DESC, last_name ASC;

2. Limiting the Result Set (LIMIT)

The LIMIT clause is used to restrict the number of rows returned by the query. It is typically used in conjunction with ORDER BY to find the “top N” or “bottom N” records.

Syntax:

SELECT column1, ...
FROM table_name
WHERE condition
ORDER BY column_name
LIMIT offset, row_count;

• row_count: The maximum number of rows to return.
• offset: (Optional) Specifies the number of rows to skip before starting to count the rows to return. Useful for pagination.

Execution Example 1: Finding the Top N

To find the names and salaries of the three highest-paid employees:

SELECT first_name, salary
FROM Employees
ORDER BY salary DESC -- Ensure they are ranked highest first
LIMIT 3;             -- Return only the first 3 rows

Execution Example 2: Pagination using OFFSET

To retrieve the second “page” of results (e.g., rows 11 through 20) where the page size is 10:

SELECT first_name, last_name
FROM Employees
ORDER BY last_name ASC
LIMIT 10 OFFSET 10; 
-- Skips the first 10 rows (the first page), then retrieves the next 10.

Note: The LIMIT clause is usually the very last clause in a SELECT statement.