DEV Community

Cheng
Cheng

Posted on

5 SQL queries developers always have to look up (with copy-paste answers)

#sql #database #webdev #productivity

Be honest — how many times have you Googled "SQL find duplicate rows" this year? Some queries just never stick in my head. Here are 5 I re-look-up constantly, with working answers you can copy. Syntax is PostgreSQL, with notes where other databases differ.

1. Find duplicate rows 

SELECT email, COUNT(*) AS count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
Enter fullscreen mode Exit fullscreen mode

That gives you the duplicated values. To pull the full duplicate rows, use a window function:

SELECT *
FROM (
  SELECT *, COUNT(*) OVER (PARTITION BY email) AS dup_count
  FROM users
) t
WHERE dup_count > 1;
Enter fullscreen mode Exit fullscreen mode

2. Get the second-highest (or Nth highest) value 

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
OFFSET 1 LIMIT 1;
Enter fullscreen mode Exit fullscreen mode

For the Nth highest, use OFFSET N-1. If you care about ties, DENSE_RANK is safer:

SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 2;
Enter fullscreen mode Exit fullscreen mode

3. Top N rows per group 

SELECT * FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn
  FROM products
) t
WHERE rn <= 3;
Enter fullscreen mode Exit fullscreen mode

This gives the 3 most expensive products in each category — the classic "top N per group" that GROUP BY alone can't do.

4. Running (cumulative) total 

SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
Enter fullscreen mode Exit fullscreen mode

5. Pivot rows into columns 

SELECT
  product,
  SUM(amount) FILTER (WHERE quarter = 'Q1') AS q1,
  SUM(amount) FILTER (WHERE quarter = 'Q2') AS q2,
  SUM(amount) FILTER (WHERE quarter = 'Q3') AS q3,
  SUM(amount) FILTER (WHERE quarter = 'Q4') AS q4
FROM sales
GROUP BY product;
Enter fullscreen mode Exit fullscreen mode

MySQL has no FILTER — use SUM(CASE WHEN quarter = 'Q1' THEN amount END) instead.

The pattern

Four of these five lean on window functions. Once those click, a lot of "hard" SQL collapses into one-liners. If you only learn one advanced SQL feature this year, make it window functions.

I got tired of re-writing these, so I collected a set of copy-paste SQL examples — each with PostgreSQL / MySQL / SQL Server / SQLite versions — here: https://forgly.dev/sql . There's also a tiny AI SQL generator that turns plain English into a query when you can't remember the syntax: https://forgly.dev/tools/ai-sql-generator . Both free, no signup.

What's the one SQL query you always have to look up?

Top comments (0)