How Can I Select Unique Combinations of Multiple Columns in SQL?

Retrieving Unique Column Combinations in SQL

This guide demonstrates how to efficiently select unique combinations of multiple columns within an SQL database. The need arises when you require distinct rows based on a specific value combination.

Method 1: Leveraging SELECT DISTINCT

The simplest approach utilizes the DISTINCT keyword. This directly selects only unique rows based on the specified columns.

For instance, to obtain unique combinations of saleprice and saledate from the sales table:

<code class="language-sql">SELECT DISTINCT saleprice, saledate FROM sales;</code>

Method 2: Combining DISTINCT with GROUP BY

While DISTINCT alone suffices for simple unique row selection, combining it with GROUP BY offers more control and flexibility, especially when further data manipulation is needed. GROUP BY groups rows based on specified columns, and DISTINCT ensures only one row per group is returned. This method is particularly useful for more complex scenarios.

Method 3: Employing Subqueries for Advanced Operations

Subqueries provide a powerful mechanism for selecting unique combinations, particularly when you need to perform additional operations on the grouped data before updating or filtering.

For example, consider updating the status column for unique sales based on saleprice and saledate:

<code class="language-sql">UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (
    SELECT id
    FROM sales S
    INNER JOIN (
        SELECT saleprice, saledate
        FROM sales
        GROUP BY saleprice, saledate
        HAVING COUNT(*) = 1
    ) T ON S.saleprice = T.saleprice AND S.saledate = T.saledate
);</code>

This query's subquery identifies unique saleprice and saledate combinations using GROUP BY and HAVING COUNT(*) = 1. The main query then updates the status of corresponding sales records to 'ACTIVE'. This approach allows for flexible data manipulation and filtering before updating the table. The HAVING clause ensures only truly unique combinations (those appearing only once) are considered.

The above is the detailed content of How Can I Select Unique Combinations of Multiple Columns in SQL?. For more information, please follow other related articles on the PHP Chinese website!