How to Efficiently Select Distinct Rows Based on Multiple Columns in SQL?

Efficiently select unique rows based on multiple columns

When working with data tables, it is often necessary to retrieve only unique rows based on specific conditions. In this example, the goal is to select all rows from the table that have a unique combination of two columns.

The original approach was to use a combination of DISTINCT and IN clauses to identify unique rows:

<code class="language-sql">UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
             FROM sales
             HAVING count = 1)</code>

However, a more efficient and reliable method is to use the GROUP BY clause:

<code class="language-sql">SELECT DISTINCT a,b,c FROM t</code>

This is equivalent to:

<code class="language-sql">SELECT a,b,c FROM t GROUP BY a,b,c</code>

The

GROUP BY clause groups rows by specified columns (in this case, saleprice and saledate) and eliminates duplicate rows. The following query will achieve the desired result:

<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 identifies unique sales rows based on saleprice and saledate and updates their status to "ACTIVE". The subquery uses the GROUP BY and HAVING clauses to select unique combinations of saleprice and saledate.

The above is the detailed content of How to Efficiently Select Distinct Rows Based on Multiple Columns in SQL?. For more information, please follow other related articles on the PHP Chinese website!