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 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 – geeksforgeeks

Now , 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 courses

Explanation: 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 ASCENDING

Explanation: 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 DESCENDING

Explanation: 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 BY

Explanation: 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.