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 explore How to SELECT DISTINCT on multiple columns in SQL by understanding various methods along with the practical implementation and so on.

How to Get Distinct Values From Multiple Columns in SQL?

When working with SQL databases, it’s common to encounter scenarios where we need to retrieve unique combinations of values from multiple columns. This is where the SELECT DISTINCT statement comes into play. Below are methods that help us to SELECT DISTINCT on multiple columns in SQL

Syntax:

SELECT DISTINCT column01, column02, ............
FROM table_name
WHERE (specify the condition if required ) ;

1. SELECT DISTINCT without WHERE Clause
2. SELECT DISTINCT with WHERE Clause
3. SELECT DISTINCT with ORDER BY clause
4. SELECT DISTINCT with COUNT() and GROUP BY clause

Creating a demo table in our database

To understand How to SELECT DISTINCT on multiple columns in SQL we need a table on which we will perform various operations and queries. Here we will consider a table called geeksforgeeks which contains id, name, score, and course as Columns.

Table – geeksforgeeks

1. SELECT DISTINCT without WHERE Clause

In this example, we are going to implement SELECT DISTINCT statement for multiple values but without using WHERE clause. We will explore each and every data of the table.

Query:

SELECT DISTINCT score, course
from geeksforgeeks ;

Output:

SELECT DISTINCT without WHERE Clause

Explanation: In the above image, we can clearly notice that all the data displayed are unique. After referring to the main table ‘geeksforgeeks’ , we can clearly see that two repeating values i.e. (150,Python), (100,Java) are eliminated in our result table.

2. SELECT DISTINCT with WHERE Clause

In this method, we are going to perform similar kind of operation as we have done in ‘method 1‘ but this time we will work with some specified data. We will use WHERE clause along with the SELECT DISTINCT statement.

Query:

SELECT DISTINCT score, course
from geeksforgeeks 
WHERE course IN ('Java','JavaScript');

Output:

SELECT DISTINCT with WHERE clause

Explanation: In the above image, we can clearly notice that all the values are unique. This is the similar kind of operation we have performed in ‘method 1’. This time we have specified that we only want data from the table where course have ‘Java’ and ‘JavaScript‘ in them.

3. SELECT DISTINCT with ORDER BY Clause

In this example, we are going to display all the distinct data from multiple columns of our table in descending order. We will use ORDER BY Clause along with DESC keyword to achieve this task.

Query:

SELECT DISTINCT score, course
FROM geeksforgeeks
ORDER BY score DESC;

Output:

SELECT DISTINCT with ORDER BY Clause

Explanation: In the above image, we can clearly see that all the data in the result table are unique/ distinct. We can notice that the data is sorted in descending order with respect to score column. We can see that score 150 comes first, followed by 100 then 50.

4. SELECT DISTINCT with COUNT() and GROUP BY Clause

In the above example, we will count distinct values considering two of the columns of the table. We will use GROUP BY clause and COUNT() function.

Query:

SELECT course,count(DISTINCT CONCAT(score, course)) as count_score_course
from geeksforgeeks
GROUP by course ;

Output:

SELECT DISTINCT – GROUP BY & COUNT()

Explanation: In the above image, we can see that two columns are displayed in the resultant image. One column is of course column and other is its corresponding unique value count. We can see that all the records have only one unique combination. We can confirm it by referring to the main table.

Conclusion

Overall, the SELECT DISTINCT statement in SQL is a powerful tool for retrieving unique combinations of values from multiple columns. By understanding the various approaches and strategies outlined in this article, you can effectively use SELECT DISTINCT on multiple columns in SQL to streamline your data querying processes and eliminate duplicate data.