ORDER BY, LIMIT, OFFSET

ORDER BY

The ORDER BY clause sorts the result set by one or more columns. By default, sorting is ascending (ASC). You can specify descending order with DESC.

Basic Syntax

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

Sample Data

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10,2),
    stock INT DEFAULT 0,
    created_at DATE
);

INSERT INTO products (name, category, price, stock, created_at)
VALUES
    ('Laptop', 'Electronics', 999.99, 25, '2024-01-10'),
    ('Headphones', 'Electronics', 149.99, 100, '2024-01-15'),
    ('Desk Chair', 'Furniture', 299.99, 15, '2024-01-20'),
    ('Monitor', 'Electronics', 549.99, 30, '2024-02-01'),
    ('Keyboard', 'Electronics', 79.99, 200, '2024-02-05'),
    ('Standing Desk', 'Furniture', 899.99, 10, '2024-02-10');

ORDER BY Examples

-- Sort by price ascending (default)
SELECT name, price FROM products ORDER BY price;

-- Sort by price descending
SELECT name, price FROM products ORDER BY price DESC;

-- Sort by name alphabetically
SELECT name, price FROM products ORDER BY name ASC;

-- Sort by multiple columns
SELECT name, category, price FROM products
ORDER BY category ASC, price DESC;
-- First sorts by category, then by price within each category

-- Sort by column position
SELECT name, price, stock FROM products ORDER BY 2 DESC;
-- Orders by the second column (price)

-- Sort with NULLs
SELECT name, created_at FROM products ORDER BY created_at;
-- NULLs typically appear first or last depending on database

ORDER BY with Expressions

-- Sort by computed value
SELECT name, price, price * stock AS inventory_value
FROM products
ORDER BY inventory_value DESC;

-- Sort by string length
SELECT name, LENGTH(name) AS name_length
FROM products
ORDER BY name_length DESC;

ORDER BY Best Practices

1. Always specify ASC/DESC for clarity
2. Use column names instead of positions when possible
3. Sort by indexed columns for better performance
4. Limit results when only top/bottom rows are needed

-- Good: Clear and explicit
SELECT name, price FROM products
WHERE category = 'Electronics'
ORDER BY price DESC;

-- Avoid: Column position (fragile)
SELECT name, price FROM products ORDER BY 2;

ORDER BY is essential for presenting data in a meaningful order.

LIMIT

The LIMIT clause restricts the number of rows returned by a query. It's useful for retrieving only the top N results or for pagination.

Basic Syntax

SELECT column1, column2
FROM table_name
LIMIT number_of_rows;

LIMIT Examples

-- Get top 5 most expensive products
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5;

-- Get first 3 products alphabetically
SELECT name, price
FROM products
ORDER BY name ASC
LIMIT 3;

-- Get the cheapest product
SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 1;

-- Get 10 most recent orders
SELECT *
FROM orders
ORDER BY order_date DESC
LIMIT 10;

LIMIT with OFFSET

The OFFSET clause skips a specified number of rows before returning results.

-- Syntax
SELECT column1, column2
FROM table_name
LIMIT number_of_rows OFFSET offset_number;

-- Or alternative syntax (MySQL, PostgreSQL)
SELECT column1, column2
FROM table_name
LIMIT offset_number, number_of_rows;

OFFSET Examples

-- Skip first 10 products, get next 5
SELECT name, price
FROM products
ORDER BY product_id
LIMIT 5 OFFSET 10;

-- Page 1 (rows 1-10)
SELECT * FROM products ORDER BY product_id LIMIT 10 OFFSET 0;

-- Page 2 (rows 11-20)
SELECT * FROM products ORDER BY product_id LIMIT 10 OFFSET 10;

-- Page 3 (rows 21-30)
SELECT * FROM products ORDER BY product_id LIMIT 10 OFFSET 20;

Pagination Pattern

-- Dynamic pagination query
-- @page = page number (1-based)
-- @per_page = items per page

SET @page = 2;
SET @per_page = 10;
SET @offset = (@page - 1) * @per_page;

PREPARE stmt FROM '
SELECT * FROM products
ORDER BY product_id
LIMIT ? OFFSET ?
';

EXECUTE stmt USING @per_page, @offset;

LIMIT Performance

-- LIMIT with ORDER BY on indexed column (fast)
SELECT * FROM products
ORDER BY product_id
LIMIT 10;

-- LIMIT with ORDER BY on non-indexed column (slower)
SELECT * FROM products
ORDER BY name
LIMIT 10;

-- Avoid: Large OFFSET values
SELECT * FROM products ORDER BY product_id LIMIT 10 OFFSET 100000;
-- This is slow because MySQL must scan 100,010 rows

Database-Specific Syntax

-- MySQL, PostgreSQL
SELECT * FROM products LIMIT 10;
SELECT * FROM products LIMIT 10 OFFSET 5;

-- SQL Server
SELECT TOP 10 * FROM products;
SELECT * FROM products ORDER BY product_id
OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;

-- Oracle
SELECT * FROM products WHERE ROWNUM <= 10;
-- Or using FETCH (Oracle 12c+)
SELECT * FROM products FETCH FIRST 10 ROWS ONLY;

LIMIT is crucial for performance optimization and implementing user-friendly pagination.

Pagination Implementation

Pagination is the practice of dividing large result sets into smaller pages. It's essential for user interfaces and API responses.

Basic Pagination Query

-- Calculate offset from page number
-- Page 1: OFFSET 0
-- Page 2: OFFSET (per_page)
-- Page 3: OFFSET (2 * per_page)
-- Formula: OFFSET = (page - 1) * per_page

-- Get page 1 (10 items per page)
SELECT * FROM products
ORDER BY product_id
LIMIT 10 OFFSET 0;

-- Get page 2
SELECT * FROM products
ORDER BY product_id
LIMIT 10 OFFSET 10;

-- Get page 3
SELECT * FROM products
ORDER BY product_id
LIMIT 10 OFFSET 20;

Count Total Records

-- Get total count for pagination metadata
SELECT COUNT(*) AS total FROM products;

-- Calculate total pages
-- total_pages = CEIL(total / per_page)

-- Combined query
SELECT 
    COUNT(*) AS total,
    CEIL(COUNT(*) / 10.0) AS total_pages
FROM products;

Complete Pagination Example

-- Variables
SET @page = 2;
SET @per_page = 10;
SET @offset = (@page - 1) * @per_page;

-- Get page of data
SELECT product_id, name, price, category
FROM products
ORDER BY product_id
LIMIT @per_page OFFSET @offset;

-- Get metadata
SELECT 
    COUNT(*) AS total_items,
    @page AS current_page,
    @per_page AS items_per_page,
    CEIL(COUNT(*) / @per_page) AS total_pages
FROM products;

Cursor-Based Pagination

For large datasets, cursor-based pagination is more efficient than offset-based.

-- Offset-based (slows down with large offsets)
SELECT * FROM products ORDER BY product_id LIMIT 10 OFFSET 100000;

-- Cursor-based (consistent performance)
SELECT * FROM products
WHERE product_id > 100000  -- Last seen ID
ORDER BY product_id
LIMIT 10;

-- Cursor for next page
SELECT * FROM products
WHERE product_id > 10010  -- Previous last ID
ORDER BY product_id
LIMIT 10;

Pagination Best Practices

1. Always ORDER BY - Pagination requires consistent ordering
2. Use indexed columns for sorting
3. Set maximum page size - Prevent overly large requests
4. Include total count - Help clients show page navigation
5. Consider cursor-based for real-time data

-- API-friendly pagination response
SELECT 
    product_id,
    name,
    price,
    category,
    COUNT(*) OVER() AS total_count
FROM products
ORDER BY product_id
LIMIT 10 OFFSET 20;

Pagination is a critical pattern for building scalable applications with large datasets.