title: Using "order by" and fetch inside a union in SQL on as400 database
body: <p>Let's say I have this table</p>
  
  <pre><code>Table name: Traffic
  Seq.     Type     Amount
  1        in       10
  2        out      30
  3        in       50
  4        out      70
  </code></pre>
  
  <p>What I need is to get the previous smaller and next larger amount of a value. So, if I have 40 as a value, I will get...</p>
  
  <pre><code>Table name: Traffic
  Seq.     Type     Amount
  2        out      30
  3        in       50
  </code></pre>
  
  <p>I already tried doing it with MYSQL and quite satisfied with the results</p>
  
  <pre><code>(select * from Traffic where
  Amount &lt; 40 order by Amount desc limit 1)
  union
  (select * from Traffic where
  Amount &gt; 40 order by Amount desc limit 1)
  </code></pre>
  
  <p>The problem lies when I try to convert it to a SQL statement acceptable by AS400. It appears that the order by and fetch function (AS400 doesn't have a limit function so we use fetch, or does it?) is not allowed inside the select statement when I use it with a union. I always get a keyword not expected error. Here is my statement;</p>
  
  <pre><code>(select seq as sequence, type as status, amount as price from Traffic where
  Amount &lt; 40 order by price asc fetch first 1 rows only)
  union
  (select seq as sequence, type as status, amount as price  from Traffic where
  Amount &gt; 40 order by price asc fetch first 1 rows only)
  </code></pre>
  
  <p>Can anyone please tell me what's wrong and how it should be? Also, please share if you know other ways to achieve my desired result.</p>