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 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?
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 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
How to Update Top 100 Records in PL/SQL?
In terms of database management, the ability to update specific subsets of data is crucial for maintaining system integrity and meeting user needs. In this article, we will understand two primary methods for updating top records. Using the ROWNUM function and Using the ORDER BY clause. Each method i
4 min read