How to SELECT DISTINCT on Multiple Columns in SQLite?
Last Updated :
08 Feb, 2024
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 explore how we can implement a SELECT DISTINCT statement to perform some real-world-like operations. We are going to explore different examples with examples.
SELECT DISTINCT on Multiple Columns in SQLite
In SQLite, the SELECT DISTINCT statement allows us to fetch unique values from one or more columns in a table. We can filter out duplicate rows in a table. In simple words, this statement returns only unique values from the specified column(s).
Syntax:
SELECT DISTINCT column_name01, column_name02,........
FROM table_name ;
Example of SELECT DISTINCT on Multiple Columns
Before moving on to the examples of "SELECT DISTINCT on multiple columns", we need a table in our database to perform relevant operations on it.
To create a table in our database, we need to run the query below.
Query:
CREATE TABLE geeksforgeeks
(
id INTEGER PRIMARY KEY,
name TEXT,
potd INTEGER,
courses INTEGER,
overall_score INTEGER
);
Now, let's add data to our table and display it.
Query:
--Data Insertion
INSERT INTO geeksforgeeks(id,name,courses,potd,overall_score)
VALUES(01,'Vishu',20,200,400);
INSERT INTO geeksforgeeks(id,name,courses,potd,overall_score)
VALUES(02,'Aayush',30,100,200);
INSERT INTO geeksforgeeks(id,name,courses,potd,overall_score)
VALUES(03,'Neeraj',40,20,250);
INSERT INTO geeksforgeeks(id,name,courses,potd,overall_score)
VALUES(04,'Vivek',20,200,400);
INSERT INTO geeksforgeeks(id,name,courses,potd,overall_score)
VALUES(05,'Harsh',30,100,200);
INSERT INTO geeksforgeeks(id,name,courses,potd,overall_score)
VALUES(06,'Sumit',NULL,100,400);
INSERT INTO geeksforgeeks(id,name,courses,potd,overall_score)
VALUES(07,'Raj',NULL,100,400);
--Displaying Data
Select * FROM geeksforgeeks ;
Output:
Table - geeksforgeeksNow , we have done with creating the table. Lets move on to the implementation part of SELECT DISCTINCT statement.
Example 1: Fetching Unique Data From Potd & Courses Columns
In this example, we are going to fetch unique data from two columns i.e. potd column and courses column. We are going to use SELECT DISTINCT statement to achieve this task. Lets see the below query.
Query:
SELECT DISTINCT potd, courses
from geeksforgeeks ;
Output:
SELECT DISTINCT on potd and coursesExplanation: We can clearly see that no two rows have the same value. Therefore we can clearly say that we have successfully achieved our task. However , we can clearly observe that "100" is repeated more than one time in potd column. But we can see that both the "100" 's have different values in its corresponding courses column. Therefore, it is considered as a valid distinct element.
Example 2: SELECT DISTINCT Along With ORDER BY Clause
CASE 1: Sorting the Data in Ascending Order
In this case, we will sort our data returned by SELECT DISTINCT statement in ascending order. We will sort our data with respect to potd column. Lets see the below query.
Query:
SELECT DISTINCT potd, courses
from geeksforgeeks
order by potd ;
Output:
SELECT DISTINCT - ORDER BY ASCENDINGExplanation: We can clearly see that our displayed output is sorted in ascending order in terms of potd column.
CASE 2: Sorting the Data in Descending Order
In this case, we will be doing the same task as we have done in case 1. The only thing we are going to change is that we will sort our data in descending order in place of ascending order.
Query:
SELECT DISTINCT potd, courses
from geeksforgeeks
order by potd DESC;
Output:
SELECT DISTINCT - ORDER BY DESCENDINGExplanation: In the above image, we can clearly see that our data has been sorted in descending order with respect to potd column.
Example 3: SELECT DISTINCT With GROUP BY Clause and Count() Function.
In this example, we are going to return count of distinct columns : potd and courses. We are also going to group them up with respect to there corresponding data on overall_score column. Lets see the query.
Query:
SELECT overall_score,count(DISTINCT CONCAT(potd, courses)) as unique_records
from geeksforgeeks
GROUP by overall_score ;
Output:
SELECT DISTINCT - GROUP BYExplanation: We can clearly see that output is displayed with overall_score and unique_records.
- For 200, there is only one combination of unique record in potd, coursescolumns i.e. (100, 30).
- For 250, there is only one combination of unique record in potd, courses columns i.e. (20, 40).
- For 400, there are two combination of unique records in potd, courses columns. They are (20, 200) and (NULL, 100).
Conclusion
Overall, the SELECT DISTINCT statement in SQLite is used for managing data retrieval by filter out duplicate rows and presenting only unique values from one or more columns. we have understood various examples its in different scenarios such as selecting distinct values from multiple columns, sorting data using the ORDER BY clause and grouping results with the GROUP BY clause. Now you have good understanding of How to SELECT DISTINCT on multiple columns in SQLite. You can easily perform queries and get the desired output.
Similar Reads
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 SQL Server?
When working with SQL Server, there are scenarios where we might need to retrieve unique combinations of values from multiple columns. This is where the SELECT DISTINCT statement comes in handy. It allows us to eliminate duplicate rows from the result set. However, using SELECT DISTINCT it on multip
4 min read
Selecting Multiple Columns Based On Condition in SQL
SQL (Structured Query Language) is used to manage and query databases. One common requirement when querying data is selecting multiple columns based on specific conditions. Understanding how to use SQL for this purpose can enhance your ability to retrieve relevant data efficiently. In this article,
4 min read
How to find distinct values of multiple columns in PySpark ?
In this article, we will discuss how to find distinct values of multiple columns in PySpark dataframe. Let's create a sample dataframe for demonstration:Python3 # importing module import pyspark # importing sparksession from pyspark.sql module from pyspark.sql import SparkSession # creating sparkses
2 min read
How to Find the Maximum of Multiple Columns in SQL
Finding the maximum value of multiple columns is one of the most common analytical tasks essential for making decisions and analyzing data. Using the MAX() function of SQL, users can find the maximum value in a single column. But to find the maximum value in multiple columns, users need to use other
2 min read
How to Get Multiple Counts With Single Query in SQLite?
In data analysis, obtaining multiple counts for different categories is a common requirement. SQLite, a lightweight and versatile database management system, offers a powerful feature that allows us to achieve this efficiently. In this article, we'll explore how to use SQLite to retrieve multiple co
3 min read
How to Find the Maximum of Multiple Columns in SQLite?
SQLite is a serverless architecture that does not require any server to perform operations and queries. It is widely used in embedded systems, mobile applications, and small-scale web applications because of its simplicity, efficiency, and portability. SQLite supports most of the standard SQL featur
4 min read
How to Specify Condition in Count() in SQLite?
In SQLite, One common question that arises is whether it's possible to specify a condition in the Count() function in SQLite. This question is particularly relevant when we want to count only certain rows based on a specific condition. We will explore different approaches to updating multiple rows e
3 min read
How to Find the Maximum of Multiple Columns in PostgreSQL?
PostgreSQL is one of the most advanced general-purpose object-relational database management systems and is open-source. Being an open-source software, its source code is available under PostgreSQL license, a liberal open-source license. Anyone with the right skills is free to use, modify, and distr
4 min read
How to Select Individual Columns in SQL?
In SQL, sometimes we require to select individual columns from a table. For this, we use a specific kind of query shown in the below demonstration. For this article, we will be using the Microsoft SQL Server as our database and Select keyword. Select is the most commonly used statement in SQL. The S
2 min read