title: MySQL syntax confusion - Merging together simple data for a graceful single-row insertion
body: <p>I am building a MySQL table to aggregate a set of particular information for a customer. Much of it I've assembled and filtered successfully with simple <code>INNER JOIN</code> commands on records with shared keys/columns, but the pivoting/transposing of the data, even being of a fixed size, is still causing me some syntax confusion for my query. The schema for this table <code>t_snapshots</code> can be seen as follows:</p>
  <pre><code>+-------------+---------------------+------+-----+---------------------+----------------+
  | Field       | Type                | Null | Key | Default             | Extra          |
  +-------------+---------------------+------+-----+---------------------+----------------+
  | id          | int(11)             | NO   | PRI | NULL                | auto_increment | 
  | value_type  | tinyint(1) unsigned | YES  |     | NULL                |                |
  | ch1_id      | varchar(20)         | YES  |     | NULL                |                |
  | ch1_val     | float               | NO   |     | 0                   |                |
  | ch2_id      | varchar(20)         | YES  |     | NULL                |                |
  | ch2_val     | float               | NO   |     | 0                   |                |
  | ch3_id      | varchar(20)         | YES  |     | NULL                |                |
  | ch3_val     | float               | NO   |     | 0                   |                |
  | ch4_id      | varchar(20)         | YES  |     | NULL                |                |
  | ch4_val     | float               | NO   |     | 0                   |                |
  | timestamp   | datetime            | NO   | MUL | current_timestamp() |                |
  +-------------+---------------------+------+-----+---------------------+----------------+
  </code></pre>
  <p>Simply put, I want to select the most recently updated value from <code>t_other_data0.ch[n]</code> to send to <code>t_snapshots.ch[n]_val</code> and select the most recently updated id from <code>t_id_pool.unit_id</code> for each unique <code>t_id_pool.channel_num</code> to send to <code>t_snapshots.ch[n]_id</code>. <code>t_id_pool.channel_num</code> is what correlates to the n value of columns <code>t_snapshots.ch[n]_val</code>:</p>
  <p>--EDIT--: Ideally, sample data from source table <code>t_other_data0</code> that looks for the latest <code>unit_id</code> value from <code>t_id_pool</code> for <code>channel_num=1,2,3,4</code> and outputs to table <code>t_snapshots</code>:</p>
  <p>Gather latest process data from <code>t_other_data0</code>. In this case, rows with <code>id</code> 5-8 are selected because they span all distinct <code>value_type</code> with the latest <code>timestamp</code>.:</p>
  <pre><code>Table: t_other_data0
  +----+------+------+------+------+------------+---------------------+
  | id | ch1  | ch2  | ch3  | ch4  | value_type | timestamp           |
  +----+------+------+------+------+------------+---------------------+
  |  1 | 1.65 | 3.25 | 1.98 | 2.17 |          1 | 2021-07-22 16:26:40 |
  |  2 | 3.12 | 2.33 | 6.42 | 3.22 |          2 | 2021-07-22 16:26:40 |
  |  3 | 2.22 | 2.24 | 3.34 | 1.17 |          3 | 2021-07-22 16:26:40 |
  |  4 | 1.52 | 1.34 |  1.9 | 2.01 |          4 | 2021-07-22 16:26:40 |
  |  5 |  3.2 | 3.21 | 5.42 | 2.13 |          1 | 2021-07-22 16:26:50 |
  |  6 | 1.55 | 1.92 | 4.32 | 4.12 |          2 | 2021-07-22 16:26:50 |
  |  7 | 2.31 | 1.93 | 2.36 |  3.4 |          3 | 2021-07-22 16:26:50 |
  |  8 | 1.78 | 2.17 | 5.62 | 2.34 |          4 | 2021-07-22 16:26:50 |
  +----+------+------+------+------+------------+---------------------+
  </code></pre>
  <p>Because these permanent channels change what temporary equipment they're tied to, we determine the current <code>unit_id</code> for each <code>channel_num</code> using latest <code>unit_id</code> from <code>t_id_pool</code>:</p>
  <pre><code>Table: t_id_pool
  +----+---------------------+-------------+---------+
  | id | timestamp           | channel_num | unit_id |
  +----+---------------------+-------------+---------+
  |  1 | 2021-07-22 09:39:09 |           1 | S4251   |
  |  2 | 2021-07-22 09:38:09 |           2 | S3552   |
  |  3 | 2021-07-22 09:38:09 |           3 | S0001   |
  |  4 | 2021-07-22 09:38:09 |           4 | S1001   |
  |  5 | 2021-07-22 09:39:10 |           1 | P5251   |
  |  6 | 2021-07-22 09:38:10 |           2 | P4552   |
  |  7 | 2021-07-22 09:38:10 |           3 | P1001   |
  |  8 | 2021-07-22 09:38:10 |           4 | P2001
  +----+---------------------+-------------+---------+
  
  </code></pre>
  <p>Output to <code>t_snapshots</code>:</p>
  <pre><code>Table: t_snapshots
  +-----+---------------------+------------+--------+---------+--------+---------+--------+---------+--------+---------+
  | id  | timestamp           | value_type | ch1_id | ch1_val | ch2_id | ch2_val | ch3_id | ch3_val | ch4_id | ch4_val |
  +-----+---------------------+------------+--------+---------+--------+---------+--------+---------+--------+---------+
  | 211 | 2021-07-14 16:26:50 |          1 | P5251  |     3.2 | P4552  |    3.21 | P1001  |    5.42 | P2001  |    2.13 |
  | 212 | 2021-07-14 16:26:50 |          2 | P5251  |    1.55 | P4552  |    1.92 | P1001  |    4.32 | P2001  |    4.12 |
  | 213 | 2021-07-14 16:26:50 |          3 | P5251  |    2.31 | P4552  |    1.93 | P1001  |    2.36 | P2001  |     3.4 |
  +-----+---------------------+------------+--------+---------+--------+---------+--------+---------+--------+---------+
  
  
  
   
  </code></pre>