Advertisement
Advertisement


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


Question

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.

2014/08/22
1
419
8/22/2014 12:07:28 AM

Accepted Answer

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
 )
2008/09/10
448
9/10/2008 3:55:08 PM


The problem with your query is that when using a GROUP BY clause (which you essentially do by using distinct) you can only use columns that you group by or aggregate functions. You cannot use the column id because there are potentially different values. In your case there is always only one value because of the HAVING clause, but most RDBMS are not smart enough to recognize that.

This should work however (and doesn't need a join):

UPDATE sales
SET status='ACTIVE'
WHERE id IN (
  SELECT MIN(id) FROM sales
  GROUP BY saleprice, saledate
  HAVING COUNT(id) = 1
)

You could also use MAX or AVG instead of MIN, it is only important to use a function that returns the value of the column if there is only one matching row.

2008/09/10

I want to select the distinct values from one column 'GrondOfLucht' but they should be sorted in the order as given in the column 'sortering'. I cannot get the distinct values of just one column using

Select distinct GrondOfLucht,sortering
from CorWijzeVanAanleg
order by sortering

It will also give the column 'sortering' and because 'GrondOfLucht' AND 'sortering' is not unique, the result will be ALL rows.

use the GROUP to select the records of 'GrondOfLucht' in the order given by 'sortering

SELECT        GrondOfLucht
FROM            dbo.CorWijzeVanAanleg
GROUP BY GrondOfLucht, sortering
ORDER BY MIN(sortering)
2018/01/13

If your DBMS doesn't support distinct with multiple columns like this:

select distinct(col1, col2) from table

Multi select in general can be executed safely as follows:

select distinct * from (select col1, col2 from table ) as x

As this can work on most of the DBMS and this is expected to be faster than group by solution as you are avoiding the grouping functionality.

2019/01/31

Source: https://stackoverflow.com/questions/54418
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]