SQL Reference Guide
Revision Time: 3 mins
18. SQL One-Liners Reference
Very popular SQL query patterns.
Second highest salary
Return: ValueFind the second highest salary without window functions.
Syntax signature:
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);Code snippet:
python
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);Expected Output:
Second maximum salary value.Remember: Always ensure correct syntax formatting when calling Second highest salary.
Remove duplicates
Return: ValueClean duplicate records, keeping the oldest/first row.
Syntax signature:
DELETE FROM t WHERE rowid NOT IN (SELECT MIN(rowid) FROM t GROUP BY col);Code snippet:
python
DELETE FROM users WHERE rowid NOT IN (SELECT MIN(rowid) FROM users GROUP BY email);Expected Output:
Unique users table.Remember: Always ensure correct syntax formatting when calling Remove duplicates.
Top N per group
Return: ValueExtract the top N highest values grouped by partition key.
Syntax signature:
SELECT * FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY grp ORDER BY val DESC) rn FROM t) WHERE rn <= N;Code snippet:
python
SELECT * FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC) rn FROM employees) WHERE rn <= 2;Expected Output:
Top 2 highest earners per department.Remember: Always ensure correct syntax formatting when calling Top N per group.
Latest record
Return: ValueGet the most recent observation in a log table.
Syntax signature:
SELECT * FROM t ORDER BY date_col DESC LIMIT 1;Code snippet:
python
SELECT * FROM logs ORDER BY created_at DESC LIMIT 1;Expected Output:
Most recent log entry.Remember: Always ensure correct syntax formatting when calling Latest record.
Running total
Return: ValueCalculate cumulative running sums.
Syntax signature:
SELECT date, amt, SUM(amt) OVER(ORDER BY date) FROM t;Code snippet:
python
SELECT order_date, amount, SUM(amount) OVER(ORDER BY order_date) as running_total FROM orders;Expected Output:
Orders logs with running totals.Remember: Always ensure correct syntax formatting when calling Running total.
Find duplicates
Return: ValueLocate keys with multiple occurrences.
Syntax signature:
SELECT col, COUNT(*) FROM t GROUP BY col HAVING COUNT(*) > 1;Code snippet:
python
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;Expected Output:
Duplicate emails list.Remember: Always ensure correct syntax formatting when calling Find duplicates.
Percentage
Return: ValueCalculate category percentage share of overall totals.
Syntax signature:
SELECT col, count * 100.0 / SUM(count) OVER() FROM t;Code snippet:
python
SELECT category, COUNT(*) * 100.0 / SUM(COUNT(*)) OVER() FROM products GROUP BY category;Expected Output:
Category percentage share values.Remember: Always ensure correct syntax formatting when calling Percentage.
Pivot rows
Return: ValueConvert row variables into column labels.
Syntax signature:
SELECT year, SUM(CASE WHEN month='Jan' THEN sales END) Jan FROM t GROUP BY year;Code snippet:
python
SELECT year, SUM(CASE WHEN month='Jan' THEN sales END) Jan FROM sales_table GROUP BY year;Expected Output:
Pivoted annual sales matrix.Remember: Always ensure correct syntax formatting when calling Pivot rows.
Count unique values
Return: ValueGet distinct items count.
Syntax signature:
SELECT COUNT(DISTINCT col) FROM t;Code snippet:
python
SELECT COUNT(DISTINCT category) FROM products;Expected Output:
Total unique categories count.Remember: Always ensure correct syntax formatting when calling Count unique values.