DISTINCT vs GROUP BY for Uniqueness
When DISTINCT is the right answer and when an interviewer expects GROUP BY instead.
DISTINCT vs GROUP BY for Uniqueness is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What DISTINCT Does
DISTINCT removes duplicate rows. The key thing interviewers check: it works on the whole selected row, not one column — so SELECT DISTINCT a, b dedupes the pair (a, b).
SELECT DISTINCT department
FROM employees;DISTINCT Spans All Selected Columns
With several columns, DISTINCT keeps each unique combination — one row per distinct (department, job_title) pair. There's no standard way to dedupe just one column.
SELECT DISTINCT department, job_title
FROM employees;What GROUP BY Does
GROUP BY collapses rows that share the same key into one per group. On its own, grouping by a column gives the same result as DISTINCT on it.
SELECT department
FROM employees
GROUP BY department;
-- equivalent to:
SELECT DISTINCT department
FROM employees;The Real Difference: Aggregation
So when do they differ? When you need an aggregate. GROUP BY can COUNT, SUM, or AVG per group; DISTINCT can't. Need unique rows? DISTINCT. Need a per-group metric? GROUP BY.
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;DISTINCT Cannot Count Per Group
You can't bolt counting onto DISTINCT. For "how many employees per department," the right tool is always GROUP BY with COUNT(*) — not SELECT DISTINCT.
-- WRONG intent: this errors or returns one total row
-- SELECT DISTINCT department, COUNT(*) FROM employees;
-- RIGHT:
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;COUNT(DISTINCT) Combines Both Ideas
A favorite combo: COUNT(DISTINCT col) counts unique values within each group. Here DISTINCT lives inside the aggregate, answering "how many distinct titles per department?"
SELECT department,
COUNT(DISTINCT job_title) AS distinct_titles
FROM employees
GROUP BY department;Counting Distinct Values Overall
Without GROUP BY, COUNT(DISTINCT col) counts unique values across the whole table — a clean answer to "how many different departments do we have?"
SELECT COUNT(DISTINCT department) AS n_departments
FROM employees;
-- equivalently, count the deduped subquery
SELECT COUNT(*)
FROM (SELECT DISTINCT department FROM employees) d;Performance Is Often a Wash
Is DISTINCT or GROUP BY faster? For plain dedup, usually equivalent — optimizers plan them alike. Pick by intent: DISTINCT for unique rows, GROUP BY when aggregating.
DISTINCT and NULL
How does DISTINCT treat NULL? It counts all NULLs as equal, so many NULL rows collapse into one. GROUP BY does the same — NULLs form a single group.
-- If manager_id has several NULLs, this returns one NULL row
SELECT DISTINCT manager_id
FROM employees;Postgres DISTINCT ON (Bonus)
Bonus: Postgres has a non-standard DISTINCT ON (expr) that keeps the first row per key given an ORDER BY. Portable code uses ROW_NUMBER() instead.
-- Postgres only: latest order per customer
SELECT DISTINCT ON (customer_id) customer_id, order_id, order_date
FROM orders
ORDER BY customer_id, order_date DESC;DISTINCT Applies to the Whole SELECT, Not One Column
One more trap: DISTINCT works on the whole row, so SELECT DISTINCT customer_id, order_date won't give one row per customer. For that, use GROUP BY with MAX or a window function.
-- Does NOT give one row per customer
SELECT DISTINCT customer_id, order_date FROM orders;
-- One row per customer: latest order date
SELECT customer_id, MAX(order_date) AS last_order
FROM orders
GROUP BY customer_id;Quick Check
Pick the right tool.
Recap
Recap: DISTINCT dedupes whole rows; GROUP BY collapses by key and enables aggregates. COUNT(DISTINCT col) counts unique values, both treat NULLs as one, and performance is usually a wash.
Frequently asked questions
Is the “DISTINCT vs GROUP BY for Uniqueness” lesson free?
Yes — the full text of “DISTINCT vs GROUP BY for Uniqueness” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.
What will I learn in “DISTINCT vs GROUP BY for Uniqueness”?
When DISTINCT is the right answer and when an interviewer expects GROUP BY instead. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Interview Prep?
No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “DISTINCT vs GROUP BY for Uniqueness” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Interview Prep lesson?
Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Projecting Columns and Aliasing Pitfalls
- Computed Columns and Expression Precedence
- DISTINCT vs GROUP BY for Uniqueness
- CASE Expressions in SELECT