Skip to content
Fix Code Error

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

March 13, 2021 by Code Error
Posted By: sheats

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
 )
Answered By: Joel Coehoorn

Related Articles

  • How can I pass a wct test while rearranging children spans…
  • Extract the different props between array of objects
  • SQL query return data from multiple tables
  • How to update other fields based on a field value in forms…
  • sql query to find priority jobs
  • data.table vs dplyr: can one do something well the other…
  • How to remove duplicate rows using CTE?
  • Ukkonen's suffix tree algorithm in plain English
  • Sort table rows In Bootstrap
  • COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better?

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

How to replace NaN values by Zeroes in a column of a Pandas Dataframe?

Next Post:

How do I set a column value to NULL in SQL Server Management Studio?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

  • Get code errors & solutions at akashmittal.com
© 2022 Fix Code Error