How to Use SQL DISTINCT and TOP in Same Query?
Last Updated :
04 Dec, 2024
Structured Query Language (SQL) is a computer language used to interact with relational databases. It allows us to organize, manage, and retrieve data efficiently. In this article, we will explain how to use the DISTINCT
keyword and the TOP
clause together in a query, explaining their purpose, usage, and providing step-by-step instructions with outputs for better clarity.
Understanding DISTINCT in SQL
The DISTINCT
keyword is used to retrieve unique values from a column, eliminating duplicate rows in the result set. This keyword is only used to return distinct or we can say different values. For example, if a column contains repeated values, DISTINCT
ensures that each value appears only once in the output.
Example
If a table contains the values ('John', 'Jane', 'John')
, using DISTINCT
will return only ('John', 'Jane')
.
Understanding TOP in SQL
The TOP
clause is used when we want to return a specific number of records or limit the number of rows returned in the result set. It is particularly useful when we need only a subset of data. However, note that not all database systems support the TOP
clause.
- In MySQL, the equivalent is
LIMIT
.
- In Oracle, the equivalent is
FETCH FIRST number ROWS ONLY
.
Example
If you have 100 rows and only need the first 10, we can use TOP 10
(or LIMIT 10
in MySQL).
Steps to Use SQL DISTINCT and TOP Together
In this section, we will cover how to use the DISTINCT
and TOP
clauses together in a SQL query to retrieve a specified number of unique records from a database table. We will walk through creating a database, inserting sample data, and applying these clauses step by step, demonstrating how to combine them for more efficient data retrieval.
Step 1: Create a Database
First, CREATE DATABASE
command initializes a new database named Student
. This will be the working database where our tables and data reside. We can verify its creation using a command like SHOW DATABASES
in MySQL.
Query:
CREATE DATABASE Student;
Step 2: Use the Student Database
Switch to the newly created Student
database. The USE
statement sets the active database context, ensuring that all subsequent operations are performed within the Student
database.
USE Student;
Step 3: Create and Insert Rows into the Student
Table
The CREATE TABLE
statement defines a table named Student
with a single column name
. Add some sample data into the Student
table. Here, we insert names, including duplicates, to demonstrate the use of DISTINCT
.
Query:
-- Create the table
CREATE TABLE Student (name VARCHAR(50));
--Insert values into the table
INSERT INTO Student VALUES ('Amit');
INSERT INTO Student VALUES ('Amit ');
INSERT INTO Student VALUES ('Aniket');
INSERT INTO Student VALUES ('Aniket');
INSERT INTO Student VALUES ('Soumya');
INSERT INTO Student VALUES ('Tridib');
Step 4: View All Rows Without Using DISTINCT
Fetch all names from the Student
table to see the raw data, including duplicates.
Query:
SELECT name FROM Student ORDER BY name;
Output
name |
Amit |
Amit |
Aniket |
Aniket |
Soumya |
Tridib |
Explanation:
This query retrieves all rows from the Student
table, ordered alphabetically by name
. Duplicate entries like '
Amit
'
and '
Amit
'
appear because DISTINCT
hasn’t been applied yet.
Step 5: View Rows Using DISTINCT
Use the DISTINCT
keyword to eliminate duplicate rows.
Query:
SELECT DISTINCT name FROM Student ORDER BY name;
Output
name |
Amit |
Aniket |
Soumya |
Tridib |
Explanation:
The DISTINCT
keyword removes exact duplicates from the result set, but it considers trailing spaces as part of the value. Hence, it will give 4 names as output of the above query.
Step 6: Combine DISTINCT with TOP
Fetch the first three unique names using DISTINCT
and TOP
.
Query:
SELECT DISTINCT TOP 3 name FROM Student ORDER BY name;
Output
Explanation:
Here, the TOP 3
clause limits the result set to three rows after applying the DISTINCT
keyword. The rows are ordered alphabetically before selection. This query demonstrates how to combine the functionalities of both clauses.
Conclusion
Using DISTINCT
and TOP
together in a query helps in retrieving a limited number of unique records from a dataset. While DISTINCT
ensures uniqueness, TOP
(or equivalent clauses like LIMIT
or FETCH
) restricts the number of results. Understanding how to use these clauses effectively enhances our SQL querying skills and improves data retrieval efficiency.
Similar Reads
How to Count Distinct Values in MySQL?
The COUNT DISTINCT function is used to count the number of unique rows in specified columns. In simple words, this method is used to count distinct or unique values in a particular column. In this article, we are going to learn how we can use the Count Distinct function in different types of scenari
5 min read
How to Count Distinct Values in PL/SQL?
PL/SQL, an extension of SQL for Oracle databases, allows developers to blend procedural constructs like conditions and loops with the power of SQL. It supports exception handling for runtime errors and enables the declaration of variables, constants, procedures, functions, packages, triggers, and mo
6 min read
How to Query Two Tables For Duplicate Values in SQL?
When working with relational databases, it's common to identify duplicate values across multiple tables. SQL provides efficient ways to query such data using different techniques. These methods help streamline data analysis and ensure data consistency. In this article, we demonstrate how to query tw
3 min read
How to SELECT DISTINCT on Multiple Columns in SQL?
In the world of databases, data duplication can lead to confusion and inefficiency. SQL provides a powerful tool, SELECT DISTINCT, to retrieve unique values from columns. However, when dealing with multiple columns, the approach becomes more detailed. In this article, we will explain how to use SELE
4 min read
How to SELECT DISTINCT on Multiple Columns in SQLite?
SQLite is a lightweight and server-less relational database management system (R.D.B.M.S). It is a self-contained database and requires very minimal configuration. It is a server-less architecture that is good for mobile applications and simple desktop applications. In this article, we are going to
4 min read
How to find first value from any table in SQL Server
We could use FIRST_VALUE() in SQL Server to find the first value from any table. FIRST_VALUE() function used in SQL server is a type of window function that results in the first value in an ordered partition of the given data set. Syntax : SELECT *, FROM tablename; FIRST_VALUE ( scalar_value ) OVER
2 min read
How to SELECT DISTINCT on Multiple Columns in PL/SQL?
PL/SQL language extends SQL by allowing procedural code within Oracle databases. It combines the power of SQL with procedural constructs like loops, conditions, and exception handling. It is a blocked programming language unit that can be named or unnamed blocks. The database does not store unnamed
3 min read
How to Update Top 100 Records in SQL?
As our systems get more complex and complex, there is often a need to update the underlying data to accommodate the evolution of the system. SQL provides a variety of ways to update the data so that the system developer can manipulate the data in whatever way necessary. In this article, we will be l
5 min read
How to Count Based on Condition in SQL Server?
In SQL Server, the COUNT() function is also utilized for tallying the number of records within a table. This article intends to explore the query, focusing on incorporating conditions into the COUNT() function in SQL Server. The COUNT() function in SQL Server is commonly utilized to count all record
4 min read
How to Update Top 100 Records in SQL Server
SQL Server is a Relational database Management system which is developed by Microsoft and is one of the most used databases in the world. It provides the developer with a set of rich functionalities to create tables, insert data in them, and then manipulate and play with them as and when necessary.
5 min read