Clever Geek Handbook

Trying to find duplicate values ​​in TWO rows and TWO columns - SQL Server

Using SQL Server I am not a DBA but can write some generic SQL. Pulled my hair for about an hour. Search I've found several solutions, but they all don't work because of the way GROUP BY works.

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

  • user ID
  • OrderDate

I am looking for lines that have BOTH userid

and orderdate

as duplicates. I want to display these lines.

If I am using a group, I cannot pull out any other data such as the order id because it is not in the group by clause.

+3


source to share


4 answers


You can use a 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)
+4


source


You can also use a window 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
+1


source


You can get duplicates using groupby and having. For example:

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
)
0


source 


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
);
0


source






More articles:


All Articles