Use of DISTINCT and TOP Clauses in SQL
💡 Concept Name
DISTINCT removes duplicate rows from the result set, while TOP limits the number of rows returned by a query.
📘 Quick Intro
DISTINCT ensures uniqueness in selected columns, while TOP fetches only the first 'n' rows based on order. Together, they improve readability, performance, and control in SQL results.
🧠 Analogy / Short Story
Imagine you’re sorting a stack of business cards. DISTINCT helps remove all the duplicate cards so you only keep one from each person. TOP is like picking only the first 5 cards from the sorted stack—giving you just a quick sample of the collection.
🔧 Technical Explanation
DISTINCTfilters out duplicate rows in the result set.TOP(n)limits the number of rows returned, usually combined with ORDER BY.DISTINCTapplies to all selected columns—if all are the same, it's considered duplicate.TOPcan be used withPERCENTandWITH TIESfor more flexibility.- Both clauses help optimize performance when only specific data is needed.
🎯 Purpose & Use Case
- ✅ Eliminate duplicate entries (e.g., unique customer cities).
- ✅ Limit output for previews or dashboards.
- ✅ Improve query efficiency by reducing rows returned.
- ✅ Combine with ORDER BY to get top-ranking values.
💻 Real Code Example
-- Remove duplicate cities
SELECT DISTINCT City FROM Customers;
-- Get top 5 highest-paid employees
SELECT TOP 5 FirstName, Salary
FROM Employees
ORDER BY Salary DESC;
-- Get top 10% most expensive products
SELECT TOP 10 PERCENT ProductName, Price
FROM Products
ORDER BY Price DESC;
❓ Interview Q&A
Q1: What does the DISTINCT clause do?
A: It removes duplicate rows from the result set based on selected columns.
Q2: Can DISTINCT be applied to multiple columns?
A: Yes, it applies to the combination of all selected columns.
Q3: What is the purpose of the TOP clause?
A: To limit the number of rows returned in a query.
Q4: How does TOP PERCENT work?
A: It returns the top x percent of rows based on the ordering clause.
Q5: What is TOP WITH TIES used for?
A: It includes additional rows that tie for the last position based on ORDER BY.
Q6: How does DISTINCT affect performance?
A: It adds overhead due to sorting or hashing but can reduce the final dataset size.
Q7: Can we use DISTINCT and TOP together?
A: Yes, but the order of clauses and intent must be clear to avoid confusion.
Q8: Is TOP standard SQL?
A: No, it's T-SQL specific. Standard SQL uses LIMIT or FETCH FIRST.
Q9: What happens if TOP is used without ORDER BY?
A: The results are nondeterministic—any random top rows could be returned.
Q10: Does DISTINCT sort the data automatically?
A: Not always—it filters duplicates but does not sort unless combined with ORDER BY.
📝 MCQs
Q1. What is the main function of DISTINCT in SQL?
- Remove nulls
- Remove duplicate rows
- Sort data
- Limit results
Q2. What does TOP 5 mean in SQL?
- Return last 5 rows
- Return first 5 rows
- Return 5 columns
- Return 5 duplicates
Q3. Can DISTINCT be used with multiple columns?
- No
- Only one column
- Yes
- Only with ORDER BY
Q4. Which keyword limits result set size in SQL Server?
- LIMIT
- TOP
- DISTINCT
- ROWNUM
Q5. What is needed for TOP clause to be meaningful?
- GROUP BY
- JOIN
- HAVING
- ORDER BY
Q6. Which clause helps fetch unique cities?
- UNION
- TOP
- DISTINCT
- SELECT ALL
Q7. What does TOP 10 PERCENT return?
- Top 10 rows
- Top 10% of ordered rows
- Every 10th row
- 10 percent of columns
Q8. Can TOP and ORDER BY be used together?
- No
- Yes
- Only in MySQL
- Only with subquery
Q9. What clause ensures uniqueness?
- WHERE
- TOP
- DISTINCT
- HAVING
Q10. Is DISTINCT a performance booster always?
- Yes, always
- No, it may add overhead
- Only in views
- Only with joins
💡 Bonus Insight
Use TOP carefully when analyzing only a subset of large datasets—without an ORDER BY, the results may not be predictable. For cross-database compatibility, prefer using LIMIT in MySQL or FETCH FIRST in ANSI SQL.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!