0

I have two tables. First one, observation, has:

Observation_Id, sender_id, lat, lon, timestamp

And second one, measurement has:

Measurement_Id, Observation_Id, type

I have many observations and many measurements. Each observation can have 1 or more measurement but just one of each type.

I want to delete every measurement row of one type (for example: type:'a') wich has same obs lat, lon and timestamp. Leaving just one of that type.

So I have some measurements of type:'a' which belong to diferent observation but those observation have same lat, lon and timestamp.

I tried with:

delete m1 from measurement m1, measurement m2 
where m1.Observation_Id > m2.Observation_Id 
AND   m1.type='a' AND m1.type = m2.type 
AND   m1.Measurement_Id <> m2.Measurement_Id;

But I think it doesn't make its work.

So, I want to have some observations repeated and I want to delete one measurement of the highest observation_id leaving the lowest one.

I think the way is about: Taking all observation which has the sender_id I want, date between period I want and timestamp repeated (count > 1)

Then Get measurements which has Observation_Id IN that select, type count > 1. And make a delete of that rows. But I don't know how to create the query

Biribu
  • 3,615
  • 13
  • 43
  • 79
  • possible duplicate of [Deleting duplicate rows from a table](http://stackoverflow.com/questions/1043488/deleting-duplicate-rows-from-a-table) – Eternal1 Sep 22 '14 at 15:59
  • I added more info about how I solve that problem but I don't have enough mysql knowledge to solve it. I think it is not similar to your post – Biribu Sep 22 '14 at 16:07

1 Answers1

0

From your query i believe that You wanted to delete the entries type a Observation_Id from measurement table. Regarding this you can use the below query to delete the whole entries of type a.

delete from observation where Observation_Id=(select Observation_Id from measurement where type=a);

In case if you wanted to retain one entry and remove others select minimum or max values of Observation_Id from observation table. and delete other entries with not equal to restriction for the minimum or maximum value.

BDRSuite
  • 1,594
  • 1
  • 10
  • 15