Duplicate MySQL rows

Question

I would like to write a query to pick out rows with the same cottageid, from, and to values, so in this case it would pick out the rows with the id 991, and 992. The table name is datesBooked.

enter image description here

Oh i forgot to add, i have over a thousand entries in this table and more than just two for cottageid 6, cottageid6 is also not the only one with multiple entries. — user1869566
– user1869566, Commented May 14, 2013 at 10:41

Alex Siri · Accepted Answer · 2013-05-14 10:40:25Z

2

You can join the table with itself:

select d1.id,d2.id from datesBooked d1
   inner join datesBooked d2 on 
      d1.cottageid=d2.cottageid
      and d1.from = d2.from
      and d1.to = d2.to
      and d1.id<d2.id

That would give you the duplicates. I am only getting the ones that have lesser id than the following one (that way you will get the duplicates in the right column, and the originals in the first one)

answered May 14, 2013 at 10:40

Alex Siri

2,8541 gold badge21 silver badges24 bronze badges

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

Comments

Aziz Shaikh · Accepted Answer · 2013-05-14 10:42:21Z

2

Try this:

SELECT * FROM datesBooked
GROUP BY cottageid, from, to
HAVING COUNT(cottageid) > 1

answered May 14, 2013 at 10:42

Aziz Shaikh

16.6k11 gold badges65 silver badges81 bronze badges

1 Comment

user1869566 Over a year ago

Sorry but this just gives me 22k results. Thanks for trying though. :)

Collectives™ on Stack Overflow

Duplicate MySQL rows

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related