# Finding duplicate data **URL:** \ **Category:** Rails\ **Created:** [December 8, 2009, 4:32pm UTC](https://www.ruby-forum.com/t/finding-duplicate-data/179950 "2009-12-08T16:32:37Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![trevoke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@trevoke](https://www.ruby-forum.com/u/trevoke)\ **Post date:** [December 8, 2009, 4:32pm UTC](https://www.ruby-forum.com/t/finding-duplicate-data/179950/1 "2009-12-08T16:32:37Z")
Hello, I have a database with the following fields: id, created\_at, updated\_at, study\_date, user\_id, modality\_id, count In other words, a rather normal Rails database. I had some issues getting ‘study\_date’ to play nice, and have ended up with some duplicate data: rows where user\_id, modality\_id, count are the same, and study\_date is … Almost the same (an hour or two off, depending on time zone adjustment). My first step will be to bring study\_date to have the same hour, so there are four identical fields for the duplicate data. Now - is there any ‘easy’ way to find and delete such duplicate data, or should I just do something like this? duplicate = [] Count.all.each do |count| duplicate \<\< count if Count.find(:first, :conditions =\> {:user\_id =\> count.user\_id (and so on) } ) end ---
**Author:** ![trevoke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@trevoke](https://www.ruby-forum.com/u/trevoke)\ **Post date:** [December 8, 2009, 5:08pm UTC](https://www.ruby-forum.com/t/finding-duplicate-data/179950/2 "2009-12-08T17:08:34Z")
Aldric G. wrote: > Hello, > I have a database with the following fields: > id, created\_at, updated\_at, study\_date, user\_id, modality\_id, count No, you’ve got a table with those fields. > In other words, a rather normal Rails database. > I had some issues getting ‘study\_date’ to play nice, and have ended up > with some duplicate data: rows where > user\_id, modality\_id, count are the same, and > study\_date is … Almost the same (an hour or two off, depending on time > zone adjustment). > > My first step will be to bring study\_date to have the same hour, so > there are four identical fields for the duplicate data. > Now - is there any ‘easy’ way to find and delete such duplicate data, or > should I just do something like this? > > duplicate = [] > Count.all.each do |count| > duplicate \<\< count if Count.find(:first, :conditions =\> {:user\_id =\> > count.user\_id (and so on) } ) > end Finding duplicates is always hard. You might be able to use SQL count and group to help locate them, but it’s not going to be that easy. Once you’ve found them, define a unique index in the DB so this can’t happen again. Best, – Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid ---
**Author:** ![trevoke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@trevoke](https://www.ruby-forum.com/u/trevoke)\ **Post date:** [December 8, 2009, 5:12pm UTC](https://www.ruby-forum.com/t/finding-duplicate-data/179950/3 "2009-12-08T17:12:03Z")
Marnen Laibow-Koser wrote: > Aldric G. wrote: > > > Hello, > > I have a database with the following fields: > > id, created\_at, updated\_at, study\_date, user\_id, modality\_id, count > > No, you’ve got a table with those fields. Indeed. > Finding duplicates is always hard. You might be able to use SQL count > and group to help locate them, but it’s not going to be that easy. > > Once you’ve found them, define a unique index in the DB so this can’t > happen again. “id” is not good enough? 😉 The problem was with my checking code, which also checked the hour - so “2009-12-05 05:00:00” was different than “2009-12-05 04:00:00” and I’d get a new field. I just finished a migration to change that field from a datetime to a date and get rid of the issue altogether. If it’s just SQL then I can do it. I was wondering if Rails had something fancy for duplicate data and couldn’t find much online. ---
**Author:** ![trevoke](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ee7513/32.png) [@trevoke](https://www.ruby-forum.com/u/trevoke)\ **Post date:** [December 8, 2009, 6:22pm UTC](https://www.ruby-forum.com/t/finding-duplicate-data/179950/4 "2009-12-08T18:22:47Z")
Aldric G. wrote: > Marnen Laibow-Koser wrote: > > > Aldric G. wrote: > > > > > Hello, > > > I have a database with the following fields: > > > id, created\_at, updated\_at, study\_date, user\_id, modality\_id, count > > > > No, you’ve got a table with those fields. > > Indeed. > > > Finding duplicates is always hard. You might be able to use SQL count > > and group to help locate them, but it’s not going to be that easy. > > > > Once you’ve found them, define a unique index in the DB so this can’t > > happen again. > > “id” is not good enough? 😉 Not if you care about duplication of other data. > The problem was with my checking code, > which also checked the hour - so “2009-12-05 05:00:00” was different > than “2009-12-05 04:00:00” and I’d get a new field. You mean a new record? > I just finished a > migration to change that field from a datetime to a date and get rid of > the issue altogether. That would do it. Now, if you don’t want two records for the same date, you need a unique index on the date field. Don’t trust the app for this. > If it’s just SQL then I can do it. I was wondering if Rails had > something fancy for duplicate data and couldn’t find much online. I’m not aware of anything, but that doesn’t mean there isn’t anything. Best, – Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid