sqlpostgresqlsql-updateduplicatesdistinct

How do I (or can I) SELECT DISTINCT on multiple columns?


I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales that are unique based on day and price will get updated to an active status.

So I'm thinking:

UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
             FROM sales
             HAVING count = 1)

But my brain hurts going any farther than that.


Solution

  • SELECT DISTINCT a,b,c FROM t
    

    is roughly equivalent to:

    SELECT a,b,c FROM t GROUP BY a,b,c
    

    It's a good idea to get used to the GROUP BY syntax, as it's more powerful.

    For your query, I'd do it like this:

    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
     )