DISTINCT vs GROUP BY — Concept Check
Questions on DISTINCT vs GROUP BY. DISTINCT and GROUP BY often produce identical results, but they diverge on ORDER BY interaction, HAVING clauses, and when aggregate functions are involved.
Questions in this quiz
8 questions on DISTINCT vs GROUP BY — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.You have a table 'users' with columns 'city' and 'created_at'. You want to list distinct cities ordered by the latest 'created_at' date. Which query should you use?
- ASELECT city FROM users GROUP BY city ORDER BY MAX(created_at) DESC;
- BSELECT city FROM users ORDER BY created_at;
- CSELECT DISTINCT city FROM users ORDER BY created_at;
- DSELECT DISTINCT city, created_at FROM users ORDER BY created_at;
2.A query using SELECT DISTINCT on a large table is slow. What might improve its performance?
- AAdding a B-tree index on the column used in DISTINCT.
- BUsing GROUP BY instead of DISTINCT without changing the index.
- CRemoving the index on the column used in DISTINCT.
- DSwitching to a hash index for the column used in DISTINCT.
3.Why might GROUP BY be preferred over DISTINCT when planning to add aggregate functions later?
- AGROUP BY queries are always faster than DISTINCT queries.
- BDISTINCT cannot be used with ORDER BY clauses.
- CDISTINCT does not work with multiple columns.
- DGROUP BY is required for HAVING clauses and aggregates.
4.Which of the following queries is incorrect and will result in a syntax error?
- ASELECT city FROM users GROUP BY city ORDER BY MAX(created_at);
- BSELECT DISTINCT city FROM users ORDER BY city;
- CSELECT city, COUNT(*) FROM users GROUP BY city HAVING COUNT(*) > 5;
- DSELECT DISTINCT city FROM users HAVING COUNT(*) > 5;
5.A developer uses SELECT DISTINCT with COUNT to find distinct products per user. Why is this incorrect?
- ADISTINCT should be applied after COUNT, not before.
- BDISTINCT and COUNT cannot be used together correctly.
- CDISTINCT should be used only with a single column.
- DGROUP BY already deduplicates user_id, making DISTINCT redundant.
6.Which query correctly lists all distinct cities from a 'users' table, leveraging an index on 'city'?
- ASELECT city FROM users ORDER BY city;
- BSELECT DISTINCT city FROM users ORDER BY city;
- CSELECT city FROM users GROUP BY city;
- DSELECT DISTINCT city FROM users;
7.A query uses SELECT DISTINCT but is unexpectedly slow. What might be a reason?
- AThe table does not have a primary key defined.
- BDISTINCT requires a B-tree index to function efficiently.
- CThe query planner is using a hash aggregate instead of an index scan.
- DThe DISTINCT clause is incompatible with the current database version.
8.What happens if you use ORDER BY on a column not in the SELECT list with DISTINCT?
- AIt works only if the column is indexed.
- BThe database automatically includes the column in the results.
- CIt results in a syntax error in most databases.
- DThe query executes but ignores the ORDER BY clause.