SQL to return rows which have duplicate values in a column

Question

I'm trying to return the rows showing OrderIds which have duplicate values in the Notes column, ie different OrderIds which have the same value in the Notes column.

I am using this query:

SELECT 
    [OrderId],
    [Notes]
FROM
    [ord].[LineItems]
GROUP BY 
    OrderId, Notes
HAVING 
    COUNT(Notes) > 1;

The problem with the above query is that it is returning false-positives, ie rows which have duplicates in the Notes column, but they have the same OrderId (there can sometimes be duplicate rows showing the same OrderIds and Notes values, because there are different values in other fields).

I only want it to return unique OrderIds which have the duplicated values in the Notes column. How can I remove these false-positives?

Sorry this has been hard to explain, and the database I'm working with isn't well normalized.

Tim Biegeleisen · Accepted Answer · 2019-07-26 08:11:17Z

3

You should be aggregating by Notes and then checking the distinct count of orders:

WITH cte AS (
    SELECT Notes
    FROM LineItems
    GROUP BY Notes
    HAVING MAX(OrderId) <> MIN(OrderId)
)

SELECT *
FROM LineItems li
WHERE EXISTS (SELECT 1 FROM cte t WHERE t.Notes = li.Notes);

answered Jul 26, 2019 at 8:11

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

forpas · Accepted Answer · 2019-07-26 09:50:03Z

1

You only need EXISTS to get all the duplicate Notes:

SELECT o.[OrderId], o.[Notes]
FROM [ord].[LineItems] o
WHERE EXISTS (
  SELECT 1 FROM [ord].[LineItems]
  WHERE [Notes] = o.[Notes] AND [OrderId] <> o.[OrderId]
)

edited Jul 26, 2019 at 9:50

answered Jul 26, 2019 at 8:26

forpas

165k10 gold badges51 silver badges85 bronze badges

Collectives™ on Stack Overflow

SQL to return rows which have duplicate values in a column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related