Скачать или смотреть How to Remove Duplicates from Different Columns in SQL

Discover how to effectively remove duplicates from different columns in an SQL table using a simple and efficient query.
---
This video is based on the question https://stackoverflow.com/q/72871319/ asked by the user 'gourav saini' ( https://stackoverflow.com/u/19487930/ ) and on the answer https://stackoverflow.com/a/72871467/ provided by the user 'Tim Biegeleisen' ( https://stackoverflow.com/u/1863229/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to remove duplicates from different columns table in sql

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Remove Duplicates from Different Columns in SQL

If you've ever dealt with datasets that contain duplicate values across different columns, you know how frustrating it can be to handle them. For instance, consider a table with two columns where values appear in reversed order in separate rows. In SQL, achieving a clean output with unique pairs can be done efficiently using a clever trick involving built-in functions. Let's break down the process step-by-step.

Understanding the Problem

Imagine we have the following data:

Column AColumn B12213443In this table, rows (1, 2) and (2, 1) represent duplicates. We want to filter this list to only display unique pairs and ignore duplicates, resulting in:

Column AColumn B1234The Solution: Using SQL Functions

To accomplish this using SQL, we can leverage the LEAST() and GREATEST() functions. Here’s how we can achieve the desired output with a simple SQL query.

Step-by-Step Breakdown

Select Unique Pairs: The LEAST() function retrieves the smaller value between two arguments, while GREATEST() retrieves the larger. By applying these functions to our columns, we can convert pairs like (1, 2) and (2, 1) into a standard format - (1, 2).

Use DISTINCT: After normalizing the pairs, we use the DISTINCT keyword to remove duplicate entries.

SQL Query

Here’s the SQL query that implements the above logic:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Query

SELECT DISTINCT: This part of the query ensures that the results contain unique rows only.

LEAST(ColumnA, ColumnB): This function retrieves the smallest value from the two columns to maintain consistency in how pairs are represented.

GREATEST(ColumnA, ColumnB): This function retrieves the largest value in the pair for the second column.

FROM yourTable: Replace yourTable with the actual name of your table.

Conclusion

By using the LEAST() and GREATEST() functions along with DISTINCT, you can effectively filter out redundant records even when they appear in different columns. This method not only simplifies the data but also saves time and resources during data processing.

So the next time you encounter a table filled with potentially duplicate values in different columns, remember this straightforward SQL trick to tidy up your data efficiently!