Find duplicate column value in sqlite

Question

I have inserted some values in the table DataTab.

SomeId: Integer     => Autogenerated primary key.
DataId: Guid
DataNumber: Integer
DataType: varchar

The above are the column's in my tables, I want to find, if the table contains repeated DataId values. It has been a long time I had worked with databases. Now I can figure out simple queries. But I found this somewhat difficult.

I tried the following query, is this correct?

SELECT * from (Select * from DataTab) AS X 
where DataId= X.DataId AND SomeId!=X.SomeId

Vignesh Kumar A · Accepted Answer · 2014-02-24 06:12:28Z

157

SELECT DataId, COUNT(*) c FROM DataTab GROUP BY DataId HAVING c > 1;

edited Feb 24, 2014 at 6:12

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

answered Feb 24, 2014 at 6:09

Jwalin Shah

2,5311 gold badge19 silver badges22 bronze badges

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

3 Comments

51k Over a year ago

And if i want to check if DataId and DataNumber both are same for a record, then the query would be as follows, SELECT DataId, COUNT(*) c FROM DataTab GROUP BY DataId,DataNumber HAVING c > 1 ,, Am i right?

Jwalin Shah Over a year ago

SELECT DataId,DataNumber,COUNT(*) c FROM DataTab GROUP BY DataId,DataNumber HAVING c > 1. because whatever fields you'r taking in group by, it should be added in select statement.

Jwalin Shah Over a year ago

Kindly upvote it as per stackoverflow guidelines if you found it is as per your expectation/ resolved your problem.

Tanveer Badar · Accepted Answer · 2019-11-19 06:23:55Z

3

I have used it in my application. It is working based on your query

SELECT a.* 
FROM CENTERDETAILS a 
JOIN 
(SELECT IPADDR, MACID, COUNT(*) 
    FROM CENTERDETAILS  
    GROUP BY IPADDR, MACID 
    HAVING count(*) > 1
) b ON a.IPADDR = b.IPADDR OR a.MACID = b.mac 
ORDER BY a.MACID

edited Nov 19, 2019 at 6:23

Tanveer Badar

5,5622 gold badges29 silver badges34 bronze badges

answered Nov 19, 2019 at 5:43

Ashok Kumar J

311 bronze badge

Comments

Wolf · Accepted Answer · 2023-12-08 15:38:34Z

0

I've tried to convert what I read in the OP as the original approach into something that works and is fairly easy to remember compared to the combination of GROUP BY and HAVING.

SELECT *
FROM DataTab a 
JOIN DataTab b ON
  a.DataId = b.DataId
WHERE
  a.SomeId <> b.SomeId

Although it may be less efficient than Jwalin Shah's answer.

answered Dec 8, 2023 at 15:38

Wolf

10.3k8 gold badges72 silver badges117 bronze badges

Collectives™ on Stack Overflow

Find duplicate column value in sqlite

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related