how do I "Order by min(subquery)"?

Question

I am trying to return a list of dates. Each row has multiple dates concatenated as a single string. I want to order the rows by the minimum date in each row.

Here is my query:

  SELECT br.bm_tracking_number,
         (SELECT TOLIST(APPT.fact_date) 
            FROM bm_fact APPT 
           WHERE APPT.bm_review_sk = br.bm_review_sk
             AND APPT.fact_type_code=183050) "Appointments"
    FROM BM_REVIEW br
   WHERE row_delete_date_time IS NULL
ORDER BY MIN(SELECT APPT.fact_date 
               FROM bm_fact APPT
              WHERE APPT.bm_review_sk = br.bm_review_sk
               AND APPT.fact_type_code = 183050);

I am getting an Oracle error (00936 missing expression) on the order by line. Is there another way to accomplish what I am trying to do?

Answer 1

Only glancing at it, but one thing is that you didn't alias bm_fact to APPT in the subquery.

EDIT:

Okay, you fixed that.

How about putting the MIN() inside the sub query?

ORDER BY (SELECT MIN(APPT.fact_date) ...)

Answer 2

This:

  SELECT br.bm_tracking_number,
         TOLIST(bf.fact_date)
    FROM BM_REVIEW br
    JOIN BM_FACT bf ON bf.bm_review_sk = br.bm_review_sk
                   AND bf.fact_type_code = 183050
   WHERE br.row_delete_date_time IS NULL
GROUP BY br.bm_tracking_number
ORDER BY MIN(bf.fact_date)

...will give you a list, ordered by the fact_date in ascending order.