MySQL新版本已經支持row_number了
但推測大大應該是舊版，所以提供舊版本變數模擬方式

select 
  `mathinelD`,
  ## 藉由rank已經篩選出唯一值 所以用min取得值就可以
  min(case when rank = 1 then `valueA` end)  as `valueA1`, 
  min(case when rank = 2 then `valueA` end)  as `valueA2`,
  min(case when rank = 3 then `valueA` end)  as `valueA3`,
  min(case when rank = 4 then `valueA` end)  as `valueA4`,
  min(case when rank = 1 then `valueB` end)  as `valueB1`,
  min(case when rank = 2 then `valueB` end)  as `valueB2`, 
  min(case when rank = 3 then `valueB` end)  as `valueB3`, 
  min(case when rank = 4 then `valueB` end)  as `valueB4`   
from (
  ## MySQL舊版本，藉由變數 + case 比較，模擬出Row_Number over功能
  SELECT T1.*,
      CASE `mathinelD` 
          WHEN @GroupCol1 THEN @curRow := @curRow + 1 
          ELSE @curRow := 1
      END AS rank
      ,@GroupCol1 := `mathinelD`
  FROM T T1
  JOIN (SELECT @curRow := 0, @GroupCol1 := '') r
  ORDER BY `mathinelD`
) temT
group by `mathinelD`

結果:

測試DDL:

CREATE TABLE T
    (`id` int, `mathinelD` varchar(7), `valueA` int, `valueB` int, `timestamp` datetime)
;
    
INSERT INTO T
    (`id`, `mathinelD`, `valueA`, `valueB`, `timestamp`)
VALUES
    (1, '00001', 11, 12, '2018-07-31 16:00:00'),
    (2, '00002', 21, 22, '2018-07-31 16:00:00'),
    (3, '00001', 31, 32, '2018-07-31 16:15:00'),
    (4, '00002', 41, 42, '2018-07-31 16:15:00'),
    (5, '00001', 51, 52, '2018-07-31 16:30:00'),
    (6, '00001', 61, 62, '2018-07-31 16:45:00')
;

SQL Fiddle

補充一下，假如mysql環境支持動態sql的話，可以在優化，不用自己算欄位數。
你先看一下，有問題跟我說

醒醒吧
你根本沒有 Group By