title: AWS Athena: Unsupported correlated subquery type but it works on Mysql and other flavors
body: <p>For each order in orders table, I'm trying to return the latest order update value based on time_sent, grouped into a single row.</p>
  <pre><code>CREATE TABLE order_updates (time_sent INTEGER, order_id INTEGER, a VARCHAR(2), b VARCHAR(2), c VARCHAR(2), d VARCHAR(2));
  
  INSERT INTO order_updates (time_sent, order_id, a, b, c, d) VALUES
  ('1', '1', 'a1', null, null, null),
  ('2', '1', null, 'b1', null, null),
  ('3', '1', null, null, 'c0', null),
  ('4', '1', 'a3', null, 'c3', null),
  ('5', '1', 'a0', null, null, null),
  ('6', '2', 'a3', 'b0', null, 'd2'),
  ('7', '2', null, 'b3', null, null);
  
  CREATE TABLE orders (order_id INTEGER, name VARCHAR(32));
  insert into orders (order_id, name) values (1, &quot;joe&quot;), (2, &quot;bill&quot;);
  
  select *, (select order_updates.a from order_updates where order_updates.order_id = orders.order_id and order_updates.a is not null order by order_updates.time_sent desc limit 1) a,
  (select order_updates.b from order_updates where order_updates.order_id = orders.order_id and order_updates.b is not null order by order_updates.time_sent desc limit 1) b,
  (select order_updates.c from order_updates where order_updates.order_id = orders.order_id and order_updates.c is not null order by order_updates.time_sent desc limit 1) c,
  (select order_updates.d from order_updates where order_updates.order_id = orders.order_id and order_updates.d is not null order by order_updates.time_sent desc limit 1) d
  FROM orders;
  </code></pre>
  <p>The above select query works fine in MySql but not in AWS Athena.</p>
  <p>Working MySQL example below:</p>
  <p><a href="https://dbfiddle.uk/?rdbms=mysql_8.0&amp;fiddle=66f17cf9f3c8f19f758c7cb1381d8207" rel="nofollow noreferrer">https://dbfiddle.uk/?rdbms=mysql_8.0&amp;fiddle=66f17cf9f3c8f19f758c7cb1381d8207</a></p>
  <p>Is there a way to accomplish the above using joins?</p>