SQL query examples

Ready-to-copy SQL for the queries developers look up most — each with the code across PostgreSQL, MySQL, SQL Server, and SQLite, plus a plain-English explanation of how it works.

Need a query for your own schema? Describe it in plain English with the AI SQL Generator →

Ranking & top-N

Find the second highest value

Get the second highest salary (or any second-largest value) in a column — the classic SQL interview question.

Find the Nth highest value

Get the Nth largest value in a column using DENSE_RANK.

Number and rank rows

Add a sequential number or rank to rows with window functions.

Get the top N rows per group

Return the highest N rows within each group (greatest-n-per-group).

Grouping & aggregation

Count rows per group

Count how many rows fall into each category with GROUP BY.

Group by month

Group rows by month in SQL — truncate a date or timestamp to year-month to count or sum records per calendar month.

Group by year

Group rows by year in SQL — extract the year from a date or timestamp to count or sum records per calendar year.

Group by week

Group rows by week in SQL — bucket records into ISO 8601 weeks (Monday start) and handle each dialect's week-numbering quirks.

Group by day

Group rows by day in SQL — strip the time off a timestamp to count or sum records per calendar day.

Group by hour

Summarize rows per hour by truncating the timestamp to the hour.

Calculate a running total

Calculate a running total in SQL — a cumulative sum that grows row by row — using the SUM() OVER (ORDER BY ...) window function, with a per-group variant.

Pivot rows into columns

Pivot rows to columns in SQL — portable conditional aggregation for MySQL, PostgreSQL, and SQLite, plus the native SQL Server PIVOT operator and PostgreSQL crosstab().

Finding & removing duplicates

Find duplicate rows

Find duplicate rows in SQL based on a single column or multiple columns — check for and count duplicate records that share the same values.

Delete duplicate rows (keep one)

Delete duplicate rows in SQL while keeping one copy — remove duplicate records in PostgreSQL, MySQL, SQL Server, and SQLite.

Joins & set operations

Find rows not in another table

SQL to find rows in one table that are not in another — the anti-join, written three ways: NOT EXISTS, NOT IN, and LEFT JOIN / IS NULL.

Dates & time

Get the day of the week

Get the day of the week from a date in SQL — the weekday number or the day name (Monday, Tuesday…) in PostgreSQL, MySQL, SQL Server, and SQLite.

Get the days between two dates

Calculate the number of days between two dates in SQL — DATEDIFF in MySQL and SQL Server, date subtraction in PostgreSQL, and julianday() in SQLite.

Get rows from the last 30 days

Filter rows whose timestamp falls within the last 30 days, with MySQL, PostgreSQL, SQL Server, and SQLite syntax.

Select rows between two dates

Select rows between two dates in SQL — filter a date or timestamp column by a date range using BETWEEN or a half-open range, with the timestamp gotcha explained.

Modifying data

Update a table from another table

Update rows using values looked up from a second table (UPDATE with JOIN).

Insert or update (upsert)

Insert a row, or update it instead when a key already exists (upsert).