I am updating an existing database to simplify it and extend its functionality. I have a table as follows:
ID Parent increment info 1 info 2
1 103 0 25 1
2 103 1 4 2
3 103 2 45 5
4 103 3 13 7
5 115 0 17 5
6 115 1 23 3
7 116 0 22 2
8 116 1 1 1
9 116 2 7 3
10 117 0 19 4
I want the new table to contain only one record for each "parent" and that record should contain for most fields, the data from the record with that parent number which has the maximum 'increment' number, but for the info 2 field it should comtain the data from the record wth the minimum 'increment'.
The desired output is as follows:
ID Parent increment info 1 info 2
4 103 3 13 1
6 115 1 23 5
9 116 2 7 2
10 117 0 19 4
Is this sort of selective merging of records possible in Mysql?
I have looked at using Group by Parent, but there are a fair number of fields to bring over, so I'm not sure that will work. I've also looked at a LEFT OUTER JOIN of the table to itself to highlight the record within each parent with the maximum increment, but that doesnt allow me to select the data fro the record with the same parent but the minimum increment.
Stumped and would really appreciate someone's help.
SELECT tt1.ID, tt1.Parent, MaxInc, tt1.info1, tt2.info2 FROM
(SELECT ID, Parent, MaxInc, info1 FROM test INNER JOIN
(SELECT Parent AS p, MAX(incr) AS MaxInc FROM test GROUP BY Parent) AS t1
ON Parent = p AND incr = MaxInc) AS tt1 INNER JOIN
(SELECT Parent, info2 FROM test INNER JOIN
(SELECT Parent AS p, MIN(incr) AS MinInc FROM test GROUP BY Parent) AS t2
ON Parent = p AND incr = MinInc) AS tt2 ON tt1.Parent = tt2.Parent
I changed increment to incr