Monday, 15 March 2010

MYSQL split out records except latest from multiple duplicates -


I am looking for hours to find it and when there are so many changes I can not look very close to my Loop on the specific requirement .... Every time I think I have found it, it goes away from me :)

So it is here;

I have imported a bunch of records in one table with one of the underlying rows, but with some duplicate data in some columns. I want to split the records into two tables - a separate table that has the latest timestamp within the 'code' group, and a table with the remaining record / P >

[Edit - Sensest forgiveness, I have to make a re-phrase because I do not think that I had clearly written clearly for the first time - in fact it was very wrong to me ... sorry!]

Cave with me There are several columns with unique ROWS - (i.e. duplicate data in each column, but the combination of all the columns in a specific line is unique - except explicitly leaving the primary key)

What do I need Row is the latest timestamp for code inside code . In the example below I have seen that I have excluded other columns in the form of these three keys;

  Table # 1 Code Area_ID Timestamp 1 2 2010-02-31 00:00:00 2 2 2012-01-31 00:00:00 2 2 2011-02 -31 00:00 : 00 1 5 2010-02-31 00:00:00 2 5 2010-02-31 00:00:00 1 2 2011-01-31 00:00:00 1 5 2012-01-31 00:00:00   

So I am trying to structure this sentence;

" 1 & amp; Area_id 2 for the combination of code, the latest timestamp 2011-01-31 00:00:00" - Return that line back.

Repeat for each combination of code and Area_ID.

Such;

  RESULT code area_im timestamp 1 2 2011-01-31 00:00:00 2 2 2012-01-31 00:00:00 1 5 2012-01-31 00:00:00 2 5 2010-02-31 00:00:00   

As I said, there are some other columns that need to be filled with the data when I split the rows, but I It seems that I can worry about it later - the first step is not to receive data on a result set without mysql / workbench time on me!

JS

and this is for table 2

  Include in Table 2 Select Table 1 to <(Code, Timestamp) (Selection Code, Maximum (Timestamp) from Table 1 Group by Code)   

And then it To delete a record from Table 1: Delete the table from

  where 1 (code, timestamp) is not (Select * from (select the code, maximum (timestamp) from the code by yourTableable Group ) S)   

Kindly See Bella (I have already turned 28 on February 31, April 31 to the 30th, I think it was a typo).

Edit

Since these queries are too slow to execute, so you can try the JOIN version:

  Select t1 in table 2 * Table 1 T1 Join the left (Selection Code, Max (Timestamp) Code by Table 1 Group from Max_Tamestamp) T2OnT1Sidod = T2Sidod and T1 TeamStamp = T.max_timestamp. Where T2 COD is the placid; Table 1 T1 Remove from the left pair (Selection code, Maximum (Timestamp) Code by Table 1 Group with maximum_temstamp) T2 on T1 Sidode = T2 Sidode and T1 TMESTamp = T. MX_Timstamp WHERE T2 COD Crack;   

Please see Bela.

In addition, you can try to add one of the following indexes:

  CREATE INDEX idx1 ON Table 1 (code) Create Index IDX2 Table 1 (Timestamp) Create Index IDX 3 Table 1 (Code, Timestamp)    

No comments:

Post a Comment