How to Get Truly Distinct Values from Multiple Columns in MySQL?

How to Select Distinct Values from Multiple Columns in MySQL

When working with databases, it's often necessary to retrieve unique or distinct values from multiple columns. However, using the simple SELECT DISTINCT clause may not always yield the desired results.

The Problem

Consider the following scenario:

Table: foo_bar

foo | bar
--- | ---
a   | c
c   | f
d   | a
c   | a
f   | c
a   | c
d   | a
a   | c
c   | a
f   | c

Running the query SELECT DISTINCT foo, bar FROM foo_bar would return the following results:

foo | bar
--- | ---
a   | c
c   | f
d   | a
c   | a
f   | c

Notice that while the values for each column are distinct, the pairs themselves are not. For instance, the pair a and c appears twice, just in a different order.

The Solution: Using GROUP BY

To obtain truly distinct pairs of values, you can utilize the GROUP BY clause:

SELECT foo, bar FROM foo_bar GROUP BY foo, bar

The GROUP BY clause instructs the database to group the results based on the specified columns. In this case, it will group the rows by both foo and bar columns.

As a result, the query will only return unique pairs of values:

foo | bar
--- | ---
a   | c
c   | f
d   | a

This approach ensures that you retrieve only unique combinations of values from the multiple specified columns.

The above is the detailed content of How to Get Truly Distinct Values from Multiple Columns in MySQL?. For more information, please follow other related articles on the PHP Chinese website!