Member-only story

Unique and Lesser Known SQL Tricks

Uncommon but extremely useful tricks

Skilled Coder
4 min readOct 19, 2024

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).

Create an account to read the full story.

The author made this story available to Medium members only.
If you’re new to Medium, create a new account to read this story on us.

Or, continue in mobile web

Already have an account? Sign in

Skilled Coder
Skilled Coder

Written by Skilled Coder

Sharing content and inspiration on programming. Coding Newsletter : https://skilledcoder.substack.com

Responses (6)

Write a response

Well done. Clear and taught me a few things! Your explanation of using correlated subquery with not exists finally ‘clicked’ in my head on how this works. Have read half a dozen explanations that fell flat before. Thank you!

Good information shared...appreciate it...thanks

A very good job. Useful stuff.