3

Using SQL Server, I'm not a DBA but I can write some general SQL. Been pulling my hair out for about an hour now. Searching I've found several solutions but they all fail due to how GROUP BY works.

I have a table with two columns that I'm trying to check for duplicates:

  • userid
  • orderdate

I'm looking for rows that have BOTH userid and orderdate as duplicates. I want to display these rows.

If I use group by, I can't pull any other data, such as the order ID, because it's not in the group by clause.

Improve this question
4
  • 4
    Welcome to SO.. Add the following data in question : sample data, expected result and the query you have tried so far Commented Jul 25, 2017 at 16:44
  • stackoverflow.com/help/how-to-ask Commented Jul 25, 2017 at 16:47
  • Learn how to ask question. Without sample data, table structure, how do expect people to help you? Commented Jul 25, 2017 at 16:48
  • This might have already been answered here: stackoverflow.com/questions/6454805/… Commented Jul 25, 2017 at 16:48

4 Answers 4

Reset to default
4

You could use the grouped query in a subquery:

SELECT *
FROM   mytable a
WHERE  EXISTS (SELECT   userid, orderdate
               FROM     mytable b
               WHERE    a.userid = b.userid AND a.orderdate = b.orderdate
               GROUP BY userid, orderdate
               HAVING   COUNT(*) > 1)
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

How would this HAVING COUNT(*) = 0 give what OP wants?
@WEI_DBA I literally don't know what I was thinking... Edited and fixed
Mureinik you're my hero. This works perfectly. I've been able to modify it to add the info I need and bingo. Thank you - seriously.
1

You can also use a windowed function:

; With CTE as 
    (Select *
    , count(*) over (partition by UserID, OrderDate) as DupRows
    from MyTable)

Select *
from CTE
where DupRows > 1
order by UserID, OrderDate
Improve this answer

1 Comment

Thanks for your help !
0

You can get the duplicates by using the groupby and having. Like so:

SELECT
    userid,orderdate, COUNT(*)
FROM
    yourTable
GROUP BY
    userid,orderdate
HAVING 
    COUNT(*) > 1

EDIT:

SELECT * FROM yourTable
WHERE CONCAT(userid,orderdate) IN
(
    SELECT
        CONCAT(userid,orderdate)
    FROM
        yourTable
    GROUP BY
        userid,orderdate
    HAVING 
        COUNT(*) > 1
)
Improve this answer

2 Comments

Yes, this will give you the duplicates, but they also want all of the columns in the result set. Good start though.
Yeah thanks for this :) This is where I started but couldn't get the rest of the columns as @WEI_DBA mentioned
0 
SELECT * 
FROM myTable 
WHERE CAST(userid as Varchar) + '/' + CONVERT(varchar(10),orderdate,103) In 
(
    SELECT 
        CAST(userid as Varchar) + '/' + CONVERT(varchar(10),orderdate,103)
    FROM myTable
    GROUP BY userid , orderdate
    HAVING COUNT(*) > 1
);
Improve this answer

2 Comments

While technically, this will get you the correct answer, the concatenation will kill performance if the data set is large. You would want to evaluate the two fields independently.
Thanks for your help !

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.