Multiple Not Distinct

Multiple NOT distinct

Select B, C
From Table
Where B In
    (Select B From Table
     Group By B
     Having Count(*) > 1)

Select non distinct rows from two columns

You are using SQL Server, so this is easier than in Access:

select A, B, C
from (select t.*, count(*) over (partition by A, B) as cnt
      from t
     ) t
where cnt > 1;

This use of count(*) is as a window function. It is counting the number of rows with the same value of A and B. The final where just selects the rows that have more than one entry.

How to select non-distinct rows with a distinct on multiple columns

select COLUMN1,COLUMN2,COLUMN3 
from TABLE_NAME 
group by COLUMN1,COLUMN2,COLUMN3 
having COUNT(*) > 1

How to Select Every Row Where Column Value is NOT Distinct

This is significantly faster than the EXISTS way:

SELECT [EmailAddress], [CustomerName] FROM [Customers] WHERE [EmailAddress] IN
  (SELECT [EmailAddress] FROM [Customers] GROUP BY [EmailAddress] HAVING COUNT(*) > 1)

SQL - Find not distinct values with two criteria

You might try this like this:

DECLARE @tbl TABLE(YourDate DATE,YourObjekt VARCHAR(100));

INSERT INTO @tbl VALUES
 ({d'2016-08-01'},'aaa')
,({d'2016-08-31'},'aaa')
,({d'2016-08-31'},'bbb')
,({d'2016-09-01'},'aaa')
,({d'2016-09-02'},'bbb');

WITH PartitionedCounted AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY YourObjekt,YearAndMonth ORDER BY YourDate) AS Nr
          ,YearAndMonth
          ,YourDate
          ,YourObjekt
    FROM @tbl AS tbl
    CROSS APPLY(SELECT CONVERT(VARCHAR(7),YourDate,102) AS YearAndMonth) AS A
)
SELECT pc.YearAndMonth,pc.YourObjekt,tbl.YourDate
FROM PartitionedCounted AS pc
INNER JOIN @tbl AS tbl ON tbl.YourObjekt=pc.YourObjekt AND CONVERT(VARCHAR(7),tbl.YourDate,102)=pc.YearAndMonth
WHERE pc.Nr > 1

UPDATE

Since you are using SQL Server 2014 you can use EOMONTH:

WITH PartitionedCounted AS
    (
        SELECT ROW_NUMBER() OVER(PARTITION BY YourObjekt,EOMONTH(YourDate) ORDER BY YourDate) AS Nr
              ,EOMONTH(YourDate) AS EOM
              ,YourDate
              ,YourObjekt
        FROM @tbl AS tbl
    )
    SELECT pc.EOM,pc.YourObjekt,tbl.YourDate
    FROM PartitionedCounted AS pc
    INNER JOIN @tbl AS tbl ON tbl.YourObjekt=pc.YourObjekt AND EOMONTH(tbl.YourDate)=pc.EOM
    WHERE pc.Nr > 1

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

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
 )

How do I select unique values from multiple columns (each value being unique not the total row)

I would do a union of queries to get all distinct telephones by column then do a query on this union of telephones to get them only once:

SELECT DISTINCT `Tel` FROM
( 
  SELECT DISTINCT `Tel1` AS `Tel` FROM products
  UNION
  SELECT DISTINCT `Tel2` AS `Tel` FROM products
  UNION
  SELECT DISTINCT `Tel3` AS `Tel` FROM products
  UNION
  SELECT DISTINCT `Tel4` AS `Tel` FROM products
) all_telephones

Select multiple columns with only one distinct column in sql

Try with GROUP BY clause and MIN function as below

SELECT CurrencyCode, MIN(BuyRate), MIN(SellRate)
FROM  Currencies
GROUP BY CurrencyCode 

Counting DISTINCT over multiple columns

If you are trying to improve performance, you could try creating a persisted computed column on either a hash or concatenated value of the two columns.

Once it is persisted, provided the column is deterministic and you are using "sane" database settings, it can be indexed and / or statistics can be created on it.

I believe a distinct count of the computed column would be equivalent to your query.

Select distinct of multiple columns from prestodb

You can use group by on all required columns:

SELECT tid, open_dt 
FROM table_name
GROUP BY tid, open_dt 

---

Related Topics