CODEWITHSUNDEEP
sqloracle-databaseoracle10gsql-order-bysubquery
AndrewL
AndrewL

Reputation: 3326

How can I use order by in a SQL subselect?

I have an aggregate function called ConcatList that concatenates a list of values into a single comma separated value.

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.

I thought I could do this:

select em.EmployeeId,
       em.EmployeeName,
       (select ConcatList(jo.JobName)
          from Job jo
         where jo.EmployeeId = em.EmployeeId
         order by jo.JobName)
  from Employee em;

However, this returns me the following error on the "order by" clause:

ORA-00907: missing right parenthesis

If I get rid of the "order by" clause, this SQL works, but I need to sort the list of jobs.

How can I make this work?

Upvotes: 0

Views: 5431

Answers (3)

xQbert
xQbert

Reputation: 35323

select em.EmployeeId,
       em.EmployeeName,
       wm_concat(jo.JobName) over 
          (partion by jo.EmployeeID order by JobName) as JobList
  from Employee em
  INNER JOIN JOB JO on jo.EmployeeId = em.EmployeeId

Example from which I realized wm_concat is an analytic --> http://www.tek-tips.com/viewthread.cfm?qid=1629662

Text2 = random text

val = grouping

Select val, text2, wm_concat(text2) over (partition by val order by text2) as out_text2 
from B


Test results:
VAL     text2                    out_Text2
1   XXX010105                   (CLOB) XXX010105
1   something XXX010101 somet   (CLOB) XXX010105,...
2   yet another XXX010102 and   (CLOB) yet anothe...
4   XXX010103                   (CLOB) XXX010103
5   a                           (CLOB) a
5   b                           (CLOB) a,b
5   c                           (CLOB) a,b,c
5   x                           (CLOB) a,b,c,x
6   a                           (CLOB) a
6   g                           (CLOB) a,g
6   i                           (CLOB) a,g,i
6   n                           (CLOB) a,g,i,n
6   x                           (CLOB) a,g,i,n,x

Upvotes: 0

muratgu
muratgu

Reputation: 7311

You can sort the joined records and then aggregate the result:

select EmployeeId,
       EmployeeName,
       ConcatList(JobName)   
from (
    select em.EmployeeId,
           em.EmployeeName,
           jo.JobName
      from Employee em 
      join Job jo
        on jo.EmployeeId = em.EmployeeId
     order by jo.JobName 
      )
group by EmployeeId,
         EmployeeName

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231651

Since ConcatList is a user-defined aggregate function, it would seem to makes sense either to modify ConcatList to order the results or to create a ConcatSortedList aggregate function that sorts the data it aggregates. Gary Myers has an example of a user-defined aggregate function that produces a sorted list.

You can also go through the various string aggregation techniques on Tim Hall's site. If you have to produce a sorted list, using many of those techniques would be easier than trying to guarantee that a non-sorting user-defined aggregate function produces a sorted result. The generic function taking a REF CURSOR, for example, is relatively easy to use when sorting is required because you can just add an ORDER BY to the cursor.

Upvotes: 1

Related Questions