title: How can I use order by in a SQL subselect?
body: <p>I have an aggregate function called ConcatList that concatenates a list of values into a single comma separated value.</p>
  
  <p>Here is an example of what I am trying to do. I have two tables: Employee and Job. Job is a "child" of Employee. I need to get a list of the employees that includes a comma-delimited list of the jobs related to the employee, sorted by the name of the job.</p>
  
  <p>I thought I could do this:</p>
  
  <pre><code>select em.EmployeeId,
         em.EmployeeName,
         (select ConcatList(jo.JobName)
            from Job jo
           where jo.EmployeeId = em.EmployeeId
           order by jo.JobName)
    from Employee em;
  </code></pre>
  
  <p>However, this returns me the following error on the "order by" clause:</p>
  
  <pre><code>ORA-00907: missing right parenthesis
  </code></pre>
  
  <p>If I get rid of the "order by" clause, this SQL works, but I need to sort the list of jobs.</p>
  
  <p>How can I make this work?</p>