Unique and Lesser Known SQL Tricks

Uncommon but extremely useful tricks

These SQL techniques can help in complex data manipulations, improve performance, and give you flexibility in handling various scenarios.

Window Functions (PARTITION BY and ROW_NUMBER)

Window functions allow you to perform calculations across a set of rows related to the current row. One particularly useful example is using ROW_NUMBER() with PARTITION BY to get the first or last record per group.

e.g Find the latest order for each customer

SELECT *
FROM (
    SELECT 
        customer_id,
        order_id,
        order_date,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as rn
    FROM orders
) as t
WHERE rn = 1;

This query assigns a row number for each order, partitioned by customer_id. Then it selects only the most recent order per customer.

Common Table Expressions (CTEs) with RECURSION

CTEs are already known, but recursive CTEs are less commonly used. They’re useful for hierarchical or tree-structured data (like employee-manager relationships or category-subcategory trees).