WHERE, ORDER BY, DISTINCT and TOP in SQL Server
Quick Answer: WHERE filters records, ORDER BY sorts records, DISTINCT removes duplicate results, and TOP limits the number of returned rows. These SQL Server clauses help us retrieve exactly the information an application needs.
1. Why Do We Need WHERE, ORDER BY, DISTINCT and TOP?
Imagine Priya manages a grocery shop with 10,000 customer orders. She wants to find expensive orders, arrange them by amount, and identify unique products.
Every order contains an order number, product, city and amount. Different customers can also purchase the same products.
How can Priya retrieve only the records she needs?
The problem without these SQL clauses
Priya can retrieve every order using a basic SELECT statement. However, the application must handle filtering, sorting and duplicate removal separately.
Example 1: Retrieve all grocery orders
Assume Priya has created the ShopOrders table with six records.
SELECT
OrderId,
Product,
City,
Amount
FROM ShopOrders;
Expected output
OrderId Product City Amount 101 Rice Bengaluru 1200 102 Sugar Hyderabad 800 103 Milk Bengaluru 600 104 Rice Chennai 1200 105 Oil Hyderabad 450 106 Milk Mysuru 300
The query returns all six orders. However, Priya still faces problems.
- Finding expensive orders requires additional filtering.
- Records do not have a requested sorting order.
- Repeated product names appear multiple times.
- Large results can return more records than needed.
Now imagine Priya managing 10,000 orders across several shop locations.
She needs database queries that handle these tasks directly.
The solution: WHERE, ORDER BY, DISTINCT and TOP
WHERE = selects rows that match a given condition.
ORDER BY = arranges returned rows in a chosen order.
DISTINCT = removes duplicate combinations from selected columns.
TOP = limits how many rows a query returns.
We use these clauses to control the records returned by SQL Server. Priya can find expensive orders and identify unique products.
Animation 1: How SQL Server processes an order query
Follow Priya's request to find the three highest qualifying grocery orders.
104: Rice · 1200
102: Sugar · 800
Priya filters four orders, sorts them and returns three. DISTINCT runs separately to list unique products.
2. Implement WHERE, ORDER BY, DISTINCT and TOP in SQL Server
Let's use Priya's six grocery orders for all four clauses.
Use WHERE to filter records
WHERE = keeps records that satisfy a specified condition.
Priya wants orders with amounts of at least 500 rupees.
Use ORDER BY to sort records
ORDER BY = arranges records using one or more columns.
ASC means ascending order. DESC means descending order. Ascending order is the default.
Use DISTINCT to remove duplicate results
DISTINCT = returns unique combinations of selected column values.
Priya wants each product name listed only once. She can use SELECT DISTINCT Product.
Use TOP to limit returned records
TOP = limits the number of rows returned by a query.
Priya wants only the three highest qualifying orders. She can use TOP (3) with sorting.
Example 2: Apply all four SQL Server clauses
Assumes ShopOrders contains the six grocery records shown earlier.
-- Filter orders
SELECT *
FROM ShopOrders
WHERE Amount >= 500;
-- Sort orders by amount
SELECT *
FROM ShopOrders
ORDER BY Amount DESC, OrderId ASC;
-- Find unique product names
SELECT DISTINCT Product
FROM ShopOrders;
-- Return the three highest qualifying orders
SELECT TOP (3) *
FROM ShopOrders
WHERE Amount >= 500
ORDER BY Amount DESC, OrderId ASC;
Notice that each query has a different purpose. The final query combines WHERE, ORDER BY and TOP.
The secondary sorting column, OrderId, gives consistent ordering when amounts match.
Expected output: Unique products
Product ------- Milk Oil Rice Sugar
Expected output: Three highest qualifying orders
OrderId Product City Amount 101 Rice Bengaluru 1200 104 Rice Chennai 1200 102 Sugar Hyderabad 800
Important: TOP without ORDER BY does not guarantee which rows SQL Server returns. Always use sorting when you need specific top records.
3. Quick Summary
Without these clauses vs with these clauses
| Without these clauses | With these clauses |
|---|---|
| All records are returned. | WHERE filters matching records. |
| Records have no requested sorting. | ORDER BY sorts records. |
| Repeated values appear. | DISTINCT returns unique combinations. |
| Large result sets are returned. | TOP limits returned rows. |
The important terms to remember
- WHERE: Filters records using a condition.
- ORDER BY: Sorts records using selected columns.
- ASC / DESC: Controls ascending or descending order.
- DISTINCT: Removes duplicate selected combinations.
- TOP: Limits the number of returned rows.
4. Common Mistakes and Best Practices
1. Using TOP without ORDER BY
SQL Server does not guarantee which records appear first without sorting.
Better approach: Use ORDER BY to define the required result order.
2. Confusing DISTINCT with filtering
DISTINCT removes duplicate combinations, not unwanted records.
Better approach: Use WHERE for conditions and DISTINCT for unique results.
3. Selecting unnecessary columns with DISTINCT
Different column combinations can still produce separate rows.
Better approach: Select only the columns needed for your unique result.
Design tip: Filter early and return only the columns your application needs. Use a clear sorting rule whenever result order matters.
5. How These SQL Clauses Come Up in Interviews
Question 1: What is the difference between WHERE and ORDER BY?
What the interviewer expects: Explain filtering and sorting.
Strong answer: "WHERE filters records based on a condition. ORDER BY arranges the matching records in ascending or descending order."
Question 2: What is the difference between DISTINCT and TOP?
What the interviewer expects: Explain duplicate removal and row limits.
Strong answer: "DISTINCT removes duplicate selected combinations. TOP limits how many rows the query returns."
Question 3: Can we use WHERE, ORDER BY and TOP together?
What the interviewer expects: Explain their combined use.
Strong answer: "Yes. We can filter orders using WHERE, sort them using ORDER BY, and return only the required number using TOP."
6. Hands-On Practice
Beginner: School student records
Retrieve students scoring above 80 marks. Sort their marks from highest to lowest.
Expected result: Show only students matching the condition.
Intermediate: Hospital appointments
Find appointments for Bengaluru patients. Display unique department names.
Hint: Combine WHERE and DISTINCT.
Advanced: Banking transactions
Find the five highest transactions for a selected account.
- Filter transactions using an account identifier.
- Sort transaction amounts in descending order.
- Return only five records.
Challenge: Handle transactions with equal amounts using secondary sorting.
7. Next Steps and Community
Previous article: SQL SELECT Statement – Retrieving Data from Tables
Next article: SQL Operators – Comparison, Logical, BETWEEN, IN and LIKE
For more details, read the official Microsoft documentation:
Try Priya's grocery order examples and create your own filtering queries. Share your solutions and discuss different SQL query approaches with other learners.