title: Using SELECT TOP 1 in subquery in outer SELECT list (SAP ASE 16)
body: <p>I have a query that works in Sybase SQL Anywhere 11 that I need to compile in SAP Adaptive Server Enterprise 16. The code doesn't want to compile in ASE though, stating that</p>
  
  <blockquote>
    <p><code>SELECT TOP</code> is not allowed in a subquery.</p>
  </blockquote>
  
  <p>Here's the query:</p>
  
  <pre><code>SELECT  a.value,
          Bcode = (SELECT TOP 1 b.code
                  FROM    Btable b
                  WHERE   b.value = a.value
                  AND     b.deleted_flag = 'N'
                  ORDER BY b.start_date DESC, b.start_time DESC),
          Bdesc = CASE
                      WHEN Bcode = 'a' THEN 'Alpha'
                      WHEN Bcode = 'b' THEN 'Beta'
                      WHEN Bcode = 'c' THEN 'Cappa'
                      WHEN Bcode = 'd' THEN 'Delta'
                      ELSE 'Epsilon'
                  END,
          a.category,
          a.status,
          a.start_date,
          a.start_time
  FROM    Atable a
  WHERE   a.deleted_flag = 'N'
  AND     a.start_date BETWEEN @a_datefrom AND @a_dateto
  AND     a.end_date IS NULL
  ORDER BY a.start_date`
  </code></pre>
  
  <p>What can I do to get the same result? Would the data need to be selected into a temp table and then manipulated via a set of atomic statements? Thanks in advance for your time and effort.</p>