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 SELECT DISTINCT on multiple columns in SQL by understanding various methods along with practical implementations. By the end, we will have a clear understanding of how to effectively retrieve unique combinations of values from multiple columns.

SELECT DISTINCT on 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 becomes invaluable. Querying distinct values helps in:

• Avoiding data duplication in results.
  
• Improving report accuracy by presenting unique data.
  
• Simplifying analysis by eliminating redundant information.

Below, we explore the syntax and multiple methods for using SELECT DISTINCT on multiple columns in SQL.

Syntax:

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

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. Here is the SQL query to create the table:

CREATE TABLE geeksforgeeks (
    id INT,
    name VARCHAR(50),
    score INT,
    course VARCHAR(50)
);

We can populate the table with sample data as follows:

INSERT INTO geeksforgeeks (id, name, score, course) VALUES
(1, 'Vishu', 150, 'Python'),
(2, 'Sumit', 100, 'Java'),
(3, 'Neeraj', 150, 'Python'),
(4, 'Aayush', 100, 'Java'),
(5, 'Vivek', 50, 'Javascript');

Output

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

Explanation:

The query eliminates duplicate rows based on the selected columns (score and course). For example, the combination (150, Python) appears twice in the original data but only once in the result.

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

Explanation:

In the above image, we can clearly notice that all values are unique. This is similar kind of operation we have performed in 'method 1'. This query retrieves distinct combinations of score and course but only for rows where course is either 'Java' or 'JavaScript'.

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

Explanation:

The query retrieves unique combinations and sorts them in descending order based on score. The result maintains uniqueness while ensuring an organized presentation of data.

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

Explanation:

This query calculates the number of unique combinations of score and course for each course. The CONCAT() function is used to create a combined string for counting unique entries.

Conclusion

The SELECT DISTINCT statement in SQL is an essential tool for retrieving unique combinations of values from multiple columns. It simplifies data queries, removes redundancy, and makes the results cleaner and more meaningful. By understanding the various approaches and strategies outlined in this article, we can effectively use SELECT DISTINCT on multiple columns in SQL to streamline our data querying processes and eliminate duplicate data.